Compile error: Method or data member not found

[OliverB]

Hi,

I have developed a spreadsheet for Excel 97/2002. The spreadsheet includes a
number of controls including a combo box (id cmbCompanyCode). This
spreadsheet has been rolled out to about 2,500 users without many problems
(except 1 user (there is always one!)).

I have recieved the message "Compile error: Method or data member not found"
when the code "If (Sheet2.cmbCompanyCode.ListIndex > 0) Then" is run in the
following code block...

Private Sub Workbook_Open()

' Get the company codes and hold them in an array...
PopulateCompanyCodes

' Show / Hide the upload button...
Sheet2.btnUploadIntoSAP.Visible =
DefaultProfitCentreFileExists(Range("DefaultProfitCentreIdsPath").Value)

If (Sheet2.cmbCompanyCode.ListIndex > 0) Then
' Set the company code from the drop down list selection...
Range("CompanyCode").Value =
arrCompanyCodes(Sheet2.cmbCompanyCode.ListIndex - 1).CompanyCode
Else
Range("CompanyCode").Value = ""
End If

End Sub

When I logged onto this machine and broke into the VBA I tried to use the
immediate window to check whether Sheet2.cmbCompanyCode exists but the
intellisense did not pick up cmbCompanyCode.

My gut feeling is that something relating to COM is screwed up thus the
combo box is not accessible but I have no idea how to fix it.

I have tried the following...
1. Refreshed the copy of the spreadsheet just in case the file is corrupt.
2. Copied the file onto another machine to see if the same problem can be
reproduced - no luck
3. Checked the references inside the vb project - exactly matched to another
machine that works okay.
4. Crawled the web any only really found a MAC converstion problem with
ActiveX controls.
5. Removed the registry settings from
HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel and had Excel rebuild
them (getting desperate!).

Any help is greatly appreciated,

Thanks

Oliver Bullock

 

[Norman Jones]

Hi Oliver,

Your error would suggest that athe ComboBox cmbCompanyCode was not found.

Try checking that the name is correctly spelt - perhaps it should be:

cmbCompanyCodes

(with a final 's') ?

 

[OliverB]

Hi Norman,

Thanks for your post. I have double checked the name of the combo box and it
matches the name I have used in code. Also, I have confirmation that this
code works on other users machines running Excel 2002 on WinXP (about 180
machines).

I do completely agree that the combo box is not being found but I get the
feeling that either a type library or OCX that is used to include a combox
box on the page is corrupt or something? I have also unregistered FM20.dll
(Forms 2 library file), replaced the file from a machine that works and
reregistered the DLL without success.

I am more convinced that this a problem with Excel and not the code but
still very unsure.

I noticed a link on the web that mentioned that installing Office 2003 on
the same machine as Office XP can cause problems but I don't think that the
problem machine has had Office 2003 anywhere near it.

Help !