Run Tim Error 429 - Active x component can't create an object

[2022]

Hi, I'm getting an error that says 'Run Tim Error 429 - Active x component can't create an object' when I switch tabs in a worksheet.

The tab I'm on is called "Page 1" but when I click out of that tab, then go back to it, I get the run time error and the code stops at the line that says "Set wb = ThisWorkbook"

I've pasted a sample of the code below - does anyone know why this would happen?

Option Explicit

Private Sub Worksheet_Activate2()

Dim wb As Workbook
Dim sh As Worksheet

Dim sh2 As Worksheet


Set wb = ThisWorkbook
Set sh = wb.Sheets("Page 1")

End Sub

 

[RoryA]

Unless you are calling it from somewhere else, that code will not run when you switch sheets since it's called Worksheet_Activate2 not Worksheet_Activate

 

[2022]

Thanks for the quick response, @RoryA

I copied the code up to that line that produces an error.

Then renamed the Sub by putting the number '2' at the end, to avoid having two subs with the same name within the same worbook.

But you're correct in pointing out that the Sub should be called 'Private Sub Worksheet_Activate()' rather than 'Private Sub Worksheet_Activate2()'

Do you know of any other reason why the code wouldn't run when moving away from the "Page 1" worksheet to another tab, then going back to "Page 1"?

Thanks in advance.

Private Sub Worksheet_Activate()

Dim wb As Workbook
Dim sh As Worksheet

Dim sh2 As Worksheet

Set wb = ThisWorkbook
Set sh = wb.Sheets("Page 1")

 

[RoryA]

The code itself looks fine, and certainly shouldn't cause that error. If it is in fact that code that is causing the error (and as I mentioned, that code shouldn't have run at all as it was), then you may have a corrupt workbook.

 

[2022]

Ok, thank you for your thoughts.

When I closed the workbook and re-opened it, then tested it again, it started working without again without the issues. But if the issue recurs, I'll reach out and let you know if I notice anything specific.

Thanks and I hope you have a Merry Christmas!

 

[RoryA]

Thank you and merry Christmas to you too!