Excel VBA Run-time Error '32809' - Trying to Understand it

Question

A colleague at work made some changes to one of our macro workbooks and now on my PC only I receive the dreaded Run-time Error '32809' when I attempt to run it. This latest version runs fine on his PC and another colleague's PC that we tested it on. The previous version runs fine on all of our PC's, all of which are running Excel 2010.

The error is thrown when the macro attempts to Select the Worksheet index 1, named "Info". I know that Select/Activate is not required but am just working with this Workbook for now and am trying to work out why I alone would receive this error.

I have tried:

1. Reboot/Power Cycle
2. Saving a Copy of the Workbook
3. Cleaning out Temp Files with CCleaner
4. Researching online
5. Checking for ActiveX Controls (Uses Form Controls)

All with no success. I then had a bit of a mess around in the immediate window and discovered that even a simple:

Debug.Print ThisWorkbook.Worksheets(1).Name

would throw the run-time error which lead me to believe that somehow that Worksheet had broke. I added a couple of events to the Worksheet including _Activate and _Change but none would fire even after confirming that:

Application.EnableEvents = True

I added a simple Test Sub as follows:

Public Sub Test()

    Dim ws As Worksheet
    Dim sheetNum As Integer

    For Each ws In ThisWorkbook.Worksheets
        ws.Select           ' Selects all Sheets Without Error
        Debug.Print ws.Name ' Prints All Worksheet Names Fine
    Next ws

    Set ws = ThisWorkbook.Worksheets(1)
    ws.Select               ' Selects Sheet 1 Without Error

    ' Prints all but sheetNum = 1, Run-time Error 32809
    For sheetNum = 7 To 1 Step -1
        Debug.Print ThisWorkbook.Worksheets(sheetNum).Name
    Next sheetNum

    ' Run-time Error 32809
    ThisWorkbook.Worksheets(1).Select

End Sub

Has anyone run into anything similar to this or know of what causes this error to occur only on some PC's?

Answer 1

In my case following helped:

1. Save file as .xlsx (macro-free) - all macros would be erased while saving;
2. Open source file with macros and copy modules to the .xlsx file;
3. Save file as .xlsm - full recompile performed.

Afterwards everything started working normally. I had file with 200+ sheets and 50+ macros and posting comments in each module didn't help, but this solution worked.

Answer 2

I've been struggling with this for awhile too. It actually occurred due to some Microsoft Office updates via Windows Update starting in December. It has caused quite a bit of a headache, not to mention hours of lost productivity due to this issue.

One of the updates breaks the forms, and you need to clear the Office cache as stated by UHsoccer

Additionally, another answer thread here: Suddenly several VBA macro errors, mostly 32809 has a link to the MS blog with details.

Another of the updates causes another error where if you create or modify one of these forms (even as simple as saving the form data) it will update the internals of the spreadsheet, which, when given to another person without the updates, will cause the error above.

The solution (if you are working with others on the same spreadsheet)? Sadly, either have everyone you deal with use the office updates, then have them clear the office cache, or revert back to pre Dec '14 updates via a system restore (or by manually removing them).

I know, not much of a solution, right? I'm not happy either.

Just as a back-story, I updated my machine, keeping up with updates, and one of the companies I dealt with did not. I was pulling out my hair just before Christmas trying to figure out the issue, and without any restore points, I finally relented and reformatted.

Now, a month later, the company's IT department updated their workstations. And, without surprise, they began having issues similar to this as well (not to mention when I received their spreadsheets, I had the same issue).

Now, we are all up on the same updates, and everything is well as can be.