Compile Error: Method or data member not found

I am having an issue where an error window pops up saying "Compile error: Method or data member not found". This happens occasionally when I open a specific excel file, but strangely not every time. Everything in the spreadsheet seems to work fine, I was just wondering if anyone knows what my issue is and why this message pops up from time to time.

Here is the VBA code:

Please Login or Register  to view this content.

The very first line, "Private Sub ComboBox1_Change()".

I'm very new to VBA and really not totally sure what I'm doing yet. This is a code I found online and it's been serving its purpose so far, but the pop-up message can get annoying. Any suggestions would be greatly appreciated!

Here is the link to the thread where I originally got the code.

https://www.excelforum.com/excel-pro...-combobox.html

Like I said, it seems to work fine, so I'm not really sure why I keep getting this error message. The spreadsheet is meant to be interactive as a quick lookup tool for different set points and tooling of machinery depending on the production line and which product is running. The code was used to make my second combobox drop-down list dependent on the selected value of the first combobox.

It worked but I have been getting this error pop-up every time I open the document. Does anybody have any idea how to make it stop? The error message highlights the first line of the code.

Please Login or Register  to view this content.

It doesn't make sense that you get a compile error when opening the file, but then the code runs correctly. Can you attach your file?

Here is the file. The compile error usually pops up, put strangely not every single time I open the spreadsheet, just most of the time.

I opened your file about 8 times and could not reproduce the error, although I did get your code to execute just fine. I am using Excel 2016 on Windows 10.

There are two strange things about your problem. First, there is no reason that VBA should be trying to compile that code when you open the file. You have no code in the ThisWorkbook module, so there would no attempt to execute anything until you either make a selection on the ref sheet, or update combobox1. VBA won't compile code unless you ask it to, or execute something. And as you said, all that code runs fine. Second, the only reason a Sub statement would fail to compile is if it had invalid syntax. "Method or data member not found" does not make sense for a Sub statement.

I'm sorry but it's a dead end for me since I can't make it happen. Maybe someone else recognizes this kind of problem.

@6StringJazzer, I thought that it seemed strange that I was getting an error on a working code. And like I said, it doesn't even happen every time which I thought to be probably the strangest thing about my problem. I don't change anything before closing the document, but for whatever reason the error continues to pop up almost every other time I open the document.

@bakerman2, Thanks for your suggestions, but it doesn't seem to matter even if I have "Lookup" as my active sheet when I save and close the file. When I go to open it again, I will still get the error every other time I open the file. I even tried adding your code to ThisWorkbook and I still get the error.

I am using Excel 2016 on Windows 7. I have attached images of what the error looks like when it pops up if that helps at all.

Thanks for your help!

Thank you for the screenshot. This is different than I thought. The Sub line is highlighted, meaning that is the line that VBA is attempting to execute, but that is not where the compile error occurs. VBA attempts to compile this code when it starts to run it, and the error is at the name highlighted in blue. This error means that it cannot find a control named ComboBox2. This does not help solve your problem though because:

1. ComboBox2 is definitely on the sheet
2. This code shouldn't even be running just from opening the file

I was able to reproduce the error using bakerman2's input that it occurs if Lookup is not the active sheet when saved and closed. I have a simple change that worked for me, but frankly I was shooting in the dark and do not know why this works. My suspicion is that there is something buggy in Excel that causes the code to execute before the controls are fully loaded on startup, and adding this line gives it time to catch up. But that still does not explain why this code is running in the first place.

This fix worked for me:

Please Login or Register  to view this content.

Thank you both for your suggestions. Unfortunately I am still getting the error after adding DoEvents to the beginning of the code. I even tried changing all of the "Me.ComboBox" to "Sheet1.Combobox" like bakerman2 suggested, but still no luck.

Thank you 6StringJazzer for explaining everything. Like I said I'm fairly new to VBA so any explanations as to how it works are appreciated.

I suppose I can just use Data Validation to get the same drop-down lists, I just hate how small the font is. At least with the ComboBoxes you can change the formatting a bit. Anyway, thanks again for all your help and suggestions.

Well, I'm not really sure why this solved my problem, but I just renamed each ComboBox to a custom name and changed the code to reflect my custom names and now this error hasn't popped up once. I don't see why that fixed it considering they were named ComboBox1 and ComboBox2 by default, so changing the name shouldn't have really made a difference. But anyway, thank you both for your help, and thank you for your last post bakerman2. It's when you said, "XL seems to forget there's an ActiveX-object called ComboBox2", that gave me the idea to try changing the names.

Problem solved!

Hello,

A bit of pretty old topic but I was having those weird issues as well on 2 diferent documents with combo boxes. All the same, all working but during opening I received that error.

I did tried the suggestion from 6StringJazzer with the 'DoEvents' and that fixxed it for me. So thank you!