Hi
I am getting:
run time automation error 2147319765(8002802b)
automation error
element not found
when trying to run this code. The first sheet is named ok, the error comes for the 2nd and subsequent sheets.
I'm using Excel 2007
Any suggestions?
Please Login or Register to view this content.
Do you get that error for any workbook or just 1 in particular?
Are you running this code in Excel?
Does the active workbook have any code in it? If so, does it compile? Are there a lot of controls on the worksheets?
Remember what the dormouse said
Feed your head
The macro works for the first sheet, returning a message box saying "Sheet1", but falls over when it comes to naming the second or subsequent sheets.
I have copied the code into another workbook and it works fine. It also used to work in the current workbook until a few days ago, but no longer does.
I'm running this in Excel. There are other macros in the modules, and some formulas and functions in the worksheets, but nothing fancy, and no active controls etc. All other macros and functions are working fine.
If you open the VB Editor and choose Debug-Compile project, do you get any errors?
debug complie yields:
"object library invalid or contains references to object definitions that could not be found "
had a quick look in tools > references but couldn't see any with "Missing" next to them
If you close Excel, search your C drive (including hidden and system folders) for *.exd files and delete them, then restart Excel, do you have the same problem?
No, alas didn't work. All the .exd files were several months old
thanks for your persistence with this
Did any .exd files get recreated after you reloaded the workbook?
yes - 3 files, each copied into 2 separate directories
files were:
Refedit.exd
MscomCtl2.exd
Msforms.exd
OK. I suspect the common controls might be the issue. Are they actually in use in the workbook?
You may also want to have a read of this - there's a link at the bottom for a safe version of the comctl2 control.
It looks like this might be the problem, but I can't these filenames in references or the c:\drive
Am I missing something - how can I tell if thety're in use?
cheers
Hi all
I'm off out for the evening but will check this thread tomorrow - about 10 hours
thanks for your help
Nicky
Last edited by NickyC; 07-13-2011 at 06:15 AM. Reason: correct typo
Do you have a reference set to the MS Windows Common Controls?
No, and it's not in the menu options in tools > references.
I found a file called mmcfx.common.resources.dll on th c:\ drive, but when I tried to add a reference to it in VBA project I got the message "Can't add a reference to the specified file"
The mscomctl32.ocx file should be in your Windows\system32 directory. Try setting a reference to it.
I found a file mscomct2.ocx that seems to contain common controls and succesully loaded it, but the macro still fails.
I have created a copy of the file and copied my code into its modules and for now the copied macro seems to be working ok. It could be some corruption to the original file or a security update that disabled a control in the file, as the website you posted to suggests.
For now I'll work with the copy, and cross fingers it doesn't happen again.
Thank you very much for your patience and persistence on this one - it seemed like the world's simplest macro, but apparently not.
Best wishes
Nicky
Glad you figured out a solution for it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks