"Object Library invalid or contains references..." in Excel VBA with DatePicker_问答_开发者

I have been working on a Excel workbook with lots of VBA code for a while and now I have send this file to some colleagues for testing and it does NOT work in their computer. We all work in the same company and have Windows XP SP2 with Office 2003.

The workbook has a form that opens when clicking over a shape and it contains some controls. When they click over the shape for the form to show the following error appears:

"Object Library invalid or contains references to object defintions that could not be found"

In the form there is a DatePicker and I think there lies the problem. If I delete the datePicker from the form and send them the file again they do not get this error message.

I already tried deleting the mscomct2.exd file 开发者_开发知识库as mentioned in this two sites "Microsoft" and "lessanvaezi" but the error stil pops. I checked and the new .exd file was generated.

Some Additional info:

I check their system and they do have the file mscomct2.ocx in the correct location(c:\Winxp\System32).
If I open a empty Excel file, go to the VBA editor go to Tools->Reference, I do NOT see the option to register the "Microsoft Common Control-2 6.0 (SP6)" (mscomct2.ocx). Instead I see a "Microsoft Windows Common Controls Satellite-3 6.2)" (cmct3de.dll).
I send my collegue the file with the datePicker, but without the reference to the "Microsoft Common Control-2 6.0 (SP6)". Before clicking the shape and opening the form I tried to reference the MSCOMCT2.ocx library dinamically with the following code. It referenced the "Microsoft Common Control-2 6.0 (SP6)" but the error still pops.

Sub RegisterCtl()

'MSComCt2.ocx
strGUID = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}"
ThisWorkbook.VBProject.References.AddFromGuid guid:=strGUID, Major:=1, Minor:=0

end Sub

If I then go to the form in the VBE editor and rightClick on the toolbox and then go to the Additional controls to add the "Microsoft Date and TimePicker" control, I see that option listed Two times. Unfortunatelly regarless of wich one I cchose, the behaviour is the same: The datepicker symbol is added in the toolbox, but when I drag the control to the form, a msgBox pops saying that the control was not available.

Has anybody an idea what is wrong? What can I do to make it run in their computer?

I appreciate any help.

Edit:

The computer with this problem has been Upgraded so I wont be able to find the specific solution for my case. I'm choosing Archers solution as it has helped the most people (Most Upvotes).

I have the same problem too. http://support.microsoft.com/kb/957924/en-us delete all .exd files make my program work!

Go into a command prompt and type the following DOS commands:

Code:

CD \Documents and Settings

DEL /S /A:H /A:-H *.EXD

I had this problem. I cant remember what the reason was, but in your userform where you have the date/time picker, put this in userform_initialize. The code just adds a textbox if they do not have the correct reference. I know its not the BEST solution, but its a workaround. I wasn't able to run regsvr32 on any machines because of our system administrators.

Dim dtP As Object
Dim hasDtPicker As Boolean
On Error Resume Next
Set dtP = frmSearch.Frame24.Controls.Add("MSComCtl2.DTPicker", "DTPicker1", True)
If Err.Number <> 0 Or dtP Is Nothing Then
     hasDtPicker = False
     'change "frmsearch.Frame24" to the area where you want the date and time picker to be.
     Set dtP = frmSearch.Frame24.Controls.Add("Forms.TextBox.1", "DTPicker1", True)
     dtP.Text = [todays_date].Value
Else
     hasDtPicker = True
End If
'
' formatting properties for both TextBox and DTPicker
'
With dtP
     .Width = 67.5
     .height = 18
     .Left = lblNextActionDate.Left
     .Top = lblNextActionDate.Top + lblNextActionDate.height + 5
End With

I think #2 is the root of the problem. If you can get mscomct2.ocx to show up in Tool - References on a new workbook, it will probably fix the rest of your problems. Did you try registering the ocx? Start - Run - cmd to open a DOS window. Navigate to the system32 folder. Type

regsvr32 mscomct2.ocx

I think that will put the ocx in the Tools - References box and hopefully that fixes it. Here's a link for insalling the ocx on Windows 7

http://www.dailydoseofexcel.com/archives/2010/05/28/calendar-control-dll-on-windows-7-64-bit/

Not your situation, but maybe helpful. Also, see MS's page on regsvr32

http://support.microsoft.com/kb/249873

I had similar problem recently. After about two days downloaded new mscomct2.ocx file: http://support.microsoft.com/kb/297381 unregistered previous one, manually removed a strange MRU key with "mscomct2" in one of fields (just in case only), registered new one (using Access ActiveX menu). And all controls from mscomct2.ocx are listed only once since then !

But unfortunately that did not remove error messages. However after double consecutive import to the new container: original_file.mdb --> db1.mdb --> db2.mdb error messages do not appear any more (in the db2.mdb file). But they were still visible during trnasfer between original_file.mdb and db1.mdb.

I am having this same problem with a file I sent to another group. I have a date picker in a user form, and the file works fine with the Excel (2007 and 2010) except for the machines in this one group. The error message is the same as in the original posting.

The answer seems to be the following, as described by a support person who looked into it:

It turns out the MSCOMCT2.OCX file was missing from the user's computer. This file is Microsoft Windows Common Controls-2 6.0 (MSCOMCT2.OCX). It appears that this control provides the capability in the model to work with the Date Picker.

The process to fix his computer was as follows:

Copy mscomct2.ocx file to c:\windows\system32
Register the ocx file by running the following command on his computer. From a command prompt type regsvr32.exe c:\windows\system32\mscomct2.ocx then press Enter.

This instruction may be specific to the group's computers, but the identification of mscomct2.ocx as the culprit is a start. I have no specific recommendation of where to get this file, except that it seems to be available on the internet.

Try to delete startup addins from here (mind Office version):

C:\Program Files\Microsoft Office\OFFICE12\XLSTART\

Just un-register MSCOMCTL.OCX and then register it.

**32-bit OS** C:\Windows\System32\MSCOMCTL.OCX

**64-Bit OS** C:\Windows\SySWOW64\MSCOMCTL.OCX

Check to see that the (Name) field on your DatePicker ActiveX control isn't incrementing. I have an excel sheet with a ComboBox in which the name changes everytime I open, and where it is referenced in code it throws this same error.

I reference ComboBox1, but strangely it is now ComboBox17.