Error when activating sheet
An excel macro workbook, which worked fine for many months, all of a sudden, started balking at this code:
Worksheets("Menu").Activat
It is in Sub Auto_Open(), because I want the "Menu" sheet to activate when the workbook is opened.
Now, when it is opened, the following error appears:
Run-time error '57121':
Application-defined or object-defined error
Even when I comment that line out, and then run it in the immediate window, I get the same error.
I don't know if this is related, but at the same time, I started getting an error when using a calendar widget on the same sheet. But I removed the calendar and this error still appears.
Any ideas? I'm perplexed.
Worksheets("Menu").Activat
It is in Sub Auto_Open(), because I want the "Menu" sheet to activate when the workbook is opened.
Now, when it is opened, the following error appears:
Run-time error '57121':
Application-defined or object-defined error
Even when I comment that line out, and then run it in the immediate window, I get the same error.
I don't know if this is related, but at the same time, I started getting an error when using a calendar widget on the same sheet. But I removed the calendar and this error still appears.
Any ideas? I'm perplexed.
Which line of code, if any, is highlighted when you press Debug when you get the message?
ASKER
This line:
Worksheets("Menu").Activat
Also, I have found that when I use this function:
Function fSheetExists(SheetName As String) As Boolean
'*************************
'Purpose: Returns True or False depending on whether or not the parameter
' Sheet is present in the workbook.
'*************************
On Error GoTo no:
WorksheetName = Worksheets(SheetName).Name
fSheetExists = True
Exit Function
no:
fSheetExists = False
End Function
to determine if "Menu" exists, it returns false! But I am looking directly at the sheet named "Menu"!!! And if I run a loop to list all sheet names, like this:
For Each ws In Worksheets
Sheets("Sheet1").Cells(x, 1) = ws.Name
Sheets("Sheet1").Cells(x, 2) = Len(ws.Name)
x = x + 1
Next ws
it does print out "Menu" with a length of 4.
Worksheets("Menu").Activat
Also, I have found that when I use this function:
Function fSheetExists(SheetName As String) As Boolean
'*************************
'Purpose: Returns True or False depending on whether or not the parameter
' Sheet is present in the workbook.
'*************************
On Error GoTo no:
WorksheetName = Worksheets(SheetName).Name
fSheetExists = True
Exit Function
no:
fSheetExists = False
End Function
to determine if "Menu" exists, it returns false! But I am looking directly at the sheet named "Menu"!!! And if I run a loop to list all sheet names, like this:
For Each ws In Worksheets
Sheets("Sheet1").Cells(x, 1) = ws.Name
Sheets("Sheet1").Cells(x, 2) = Len(ws.Name)
x = x + 1
Next ws
it does print out "Menu" with a length of 4.
Please upload your file, it will be more easy to fix the error.
Does adding ThisWorkbook like this make any difference.
ThisWorkbook.Worksheets("Menu").ActivateASKER
No, that results in the same error.
I will try and see if I can upload it. It is for work, so I may not be able to modify it enough.
I will try and see if I can upload it. It is for work, so I may not be able to modify it enough.
Please try this, if the number of sheet for "MENU" is 4,
Application.ActiveWorkbook
And, check if it works.
If error, please use following code to check which workbook is activate
Msgbox Application.ActiveWorkbook
Application.ActiveWorkbook
And, check if it works.
If error, please use following code to check which workbook is activate
Msgbox Application.ActiveWorkbook
This kind of error can result if your worksheet has one or more spaces after Menu its sheet tab. Click in the sheet tab and see if you can move the cursor to the right of Menu and observe a gap. If so, there's your problem.
For us to debug the workbook, you may delete all the data from the menu worksheet, as well as all the other worksheets. As long as the stripped down workbook still reproduces the error when the Auto_Open sub runs, we can debug your code.
For us to debug the workbook, you may delete all the data from the menu worksheet, as well as all the other worksheets. As long as the stripped down workbook still reproduces the error when the Auto_Open sub runs, we can debug your code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.