Search code examples
vbaerror-handlingexcel-2007

What is causing "Automation error Unspecified error" on Worksheet_Activate?

I have a worksheet named "Dates" (object name is A_Dates) that needs to be calculated when it is activated (It may be worth noting that this is in my Personal macro workbook). I regularly have workbooks open that have too many calculations in the for me to have auto-calculation on. So I have auto-calc set to manual, and the following code in the worksheet:

Private Sub Worksheet_Activate()
   A_Dates.Calculate
End Sub

This has worked fine for the last 3 months, day-in and day-out. Yesterday, it stopped working. It now throws this error on the declaration line:

Microsoft Visual Basic
Automation error
Unspecified error
[OK] [Help]

I have tried changing how I reference the sheet, using:

Sheets("Dates").Calculate

and

ActiveSheet.Calculate

to no avail. I've also included error handling:

On Error Resume Next

which doesn't prevent it. I've even gone so far as:

Private Sub Worksheet_Activate()
   On Error GoTo headache
   Sheets("Dates").Calculate
Exit Sub

headache:
   Exit Sub
End Sub

and it still shows up. I am totally at a loss. Help?

Additional Information

I have the following references, and use all of them in various macros in this workbook:

Visual Basic for Applications

  • Microsoft Excel 12.0 Object Library
  • OLE Automation
  • Microsoft Office 12.0 Object Library
  • Microsoft Scripting Runtime
  • Microsoft Forms 2.0 Object Runtime
  • Microsoft HTML Object Library
  • Microsoft Internet Controls
  • Microsoft ActiveX Data Objects 2.8 Library
  • Microsoft ActiveX Data Objects Recordset 2.8 Library
Solution

  • I found the issue. One of my forms had a control on it that apparently stopped working, and this had a ripple effect.

    The control in question was "Microsoft ProgressBar Control, version 6.0". I do not know WHY it stopped working, but removing the form (and, of course, all references to it) resolved the issue.