Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Method or data member not found
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("DefaultProfit CentreIdsPath").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\E xcel and had Excel rebuild them (getting desperate!). Any help is greatly appreciated, Thanks Oliver Bullock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Method or data member not found
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') ? --- Regards, Norman "OliverB" wrote in message ... 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("DefaultProfit CentreIdsPath").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\E xcel and had Excel rebuild them (getting desperate!). Any help is greatly appreciated, Thanks Oliver Bullock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error: Method or data member not found
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 ! -- Oliver Bullock "Norman Jones" wrote: 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') ? --- Regards, Norman "OliverB" wrote in message ... 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("DefaultProfit CentreIdsPath").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\E xcel and had Excel rebuild them (getting desperate!). Any help is greatly appreciated, Thanks Oliver Bullock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile error: Method or data member not found | Excel Worksheet Functions | |||
Compile Error Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming | |||
Compile Error: Method or data member not found | Excel Programming |