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.