VBA runtime error

[dmcnab]

Hi loungers....I have 12 Excel Professional 2010 files being used by 12 different people.....they are all created from the same template; they have about 8 different worksheets.....the files are stored on a network drive and opened from that drive (and saved back to the drive)....2 users report seeing the following error message:

Microsoft Visual Basic
Runtime error 32809
Application-defined or object-defined error

......my Google research (limited) suggests this is caused by the Excel program and that it may need to be installed.....I can't replicate this error when I open these files, so it's a bit hard to figure out what is happening......anyone have any ideas or thoughts on this? Thanks.

[HansV]

Can you find out from those users which line of code causes the error?

[HansV]

Just a wild guess - it might be related to a faulty update released by Microsoft on December 9, 2014. See Form Controls stop working after December 2014 Updates.

A possible solution:

Quit all Office applications.
Start Windows Explorer.
Enter %temp% in the address bar, then press Enter.
Use the Search box to search for *.exd
Delete all found files.
You can also open the following folders within the Temp folder and delete the .exd files in them:
- Excel8.0
- VBE
- Word8.0
Restart Windows.

(Thanks to Rory for pointing to the .exd files)

[dmcnab]

Good morning, Hans...I will have one of the users run the macro and then try to get the line that is causing the error......and I will also try the solution suggested above.....the weird part is that one of the users can open all of her files and the macros run without problem....and yet if she opens one of the 'non-working' files, she also gets that same error....this makes me believe that the problem is related to the file itself (rather than Excel etc)..I will get back to you. Thanks.

[rory]

What if she opens one of the problem files, compiles it and then saves and reopens it?

[dmcnab]

Not sure what you mean by 'compiles it'....2 pieces of info: (1)there are no ActiveX controls on the worksheet; (2) ..........and the possible solution that Hans suggested earlier didn't work

The line causing the problem is the first one in the macro (ActiveSheet.Unprotect Password:="secret")...I made sure that the password is correct...........I took one of the 12 files that is working properly (File #good), and copied data from the 'bad' file into the good file and renamed it File#good-new.....all macros were working properly for the user of File#good, and they worked properly for me once I re-constituted it as File#good-new..............but the problem re-appeared once the original used of the 'bad' file opened File#good-new, leading me to suspect that the problem has something to do with the installation of Excel on her computer (ie: it works fine for everyone except that user), so I am thinking that re-installing Excel (or maybe MS Office as a whole) is the answer to this...??..??

[HansV]

Have you asked the user who has problems to look at the steps I outlined?

[dmcnab]

I walked her thru it over the phone, and she deleted the three *.exd files.....but couldn't even find a Temp folder, and b/c I couldn't see exactly what she was doing, I didn't go any further...

[HansV]

Would it be possible for you to visit this user?

[dmcnab]

That's my next step....have an IT staff member go to her computer etc and discuss it with that person...I am also going to have her logon to the network from another computer and see whether the problem persists (if it does, I infer it is the Excel file; if not, I infer it is her Excel program...)...I iwll let you know what I discover...thanks!

[syswizard]

Don't forget there is also a "Repair Microsoft Office" option within Control Panel, Programs/Program Settings.

[dmcnab]

Thanks syswizard.....forgot about this, but tried it and it didn't work.....looking more and more like a re-install, altho I want to have an on-site IT person take a look at things first...will let you know what happens.

[greevek]

Hi

Did you get an answer to this issue as I am seeing it on a number of sheets we have been using. It happens when opening the sheet initially so I found a piece of code on another forum that inserted into the worsheet to run on opening the sheet alongside the other code that was running on opening

Code: Select all

Application.OnTime Now() + TimeSerial(0, 0, 2)

This stopped the error messgae as it slows the code down from running until it is ready to. The problem I have now is it still does it on closing the sheet down so I am only half way to sorting my issue out.

Someone else has suggested that maybe copy the tabs and name the old tab something random so you can allow the new tab to have the existing name and then delete the old tab, this kind of sorted some of my issue but the sheet will not let me delete the tab without crashing so no goo there either.

There must be a solution for this as it's causing my sheets, that did work really well, to just stop working

[HansV]

Welcome to Eileen's Lounge!

Do you know which line of code causes the error?

[dmcnab]

As far as I can tell, the source of the problem was a series of Microsoft updates that were pushed out in late December...the solution (actually, more of a work-around) in my case was as follows:
(1) the problem seemed to start with the VBA code I wrote, the 1st line of which called for unprotecting of the active sheet. The 'unprotecting' required a password, and so I removed the need for a password to protect or unprotect the sheet.
(2) Then, I made sure that all of the Excel updates had been installed, and once that was done, the error message stopped.

I can't even begin to explain 'how' all of this worked, but it was a suitable enough workaround....I'm not totally happy about having 2 sheets (in a 10 sheet workbook) without password protection, but the sheets are at least protected (so that formulas can't be accidentally deleted) and this is working so far...hope that some of this helps.