"Object Library invalid or contains references..." in Excel VBA with DatePicker

Question

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:

1. I check their system and they do have the file mscomct2.ocx in the correct location(c:\Winxp\System32).
2. 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).
3. 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

1. 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).

Solution 1

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

Solution 2

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

Solution 3

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.

Solution 4

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

Solution 5

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.

View more solutions