Lỗi compile error method or datamember not found
|
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! Show
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. - 08-24-2017, 09:27 PMRe: Compile Error: Method or data member not foundThe only time that I could reproduce the error is when sheets Lookup isn't the active sheet when the file is saved and closed. This seems to be a solution. In ThisWorkbook. and your code like this. Avoid using Select, Selection and Activate in your code. Use With ... End With instead. You can show your appreciation for those that have helped you by clicking the \ at the bottom left of any of their posts. - 08-25-2017, 08:02 AMRe: Compile Error: Method or data member not found@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! - 08-25-2017, 08:32 AMRe: Compile Error: Method or data member not foundAs I already said I can only reproduce the error when Lookup isn't the active sheet on closing. Otherwise all works just fine. Seems you didn't try the second code when I see your screenshot. Which References do you have activated ? -
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: Re: Compile Error: Method or data member not foundThank 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. - 08-25-2017, 12:08 PMRe: Compile Error: Method or data member not foundMy best guess it's an xml-thing because on closing the workbook XL seems to forget there's an ActiveX-object called ComboBox2 is present. -
Re: Compile Error: Method or data member not foundWell, 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! -
Re: Compile Error: Method or data member not foundHello, 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. |
