asked on

Runtime error 1004 - Application defined error

I am getting and Runtime error 1004 - Application defined error after the Else statement below. Sheet8 is hidden and it is suppose to Unhide the sheet.

Public Function GoMenu(Optional Awhere As String) As Boolean
GoMenu = False
If Awhere = "" Then
Sheet01.Select
Sheet01.Range("StatRoll").Select
Else
If ActiveWorkbook.Worksheets(Awhere).Visible = xlHidden Then ActiveWorkbook.Worksheets(Awhere).Visible = True
ActiveWorkbook.Worksheets(Awhere).Select
ActiveWorkbook.Worksheets(Awhere).Activate
ActiveWorkbook.Worksheets(Awhere).Range("B3").Select
End If
GoMenu = True
End Function

nutsch

Are you sure that there is a sheet named with the passed string?
You have to make sure to pass the sheet's tab name, not the code name.

Thomas

Saqib Husain

And the spelling should be an exact match. Any leading, trailing or extra space would make it fail.

leezac

ASKER

When I click F4 to view the properties

(Name) is Sheet08
and Name is Maintenace

When I try to select the dropdown manually to unhide for the Visible property - I get "Unable to set the Visible property of the Worksheet class" error message

nutsch

So the AWhere string should be set to Maintenace, not Sheet08, is that what you have?

leezac

ASKER

Public Function GoMenu(Optional Awhere As String) As Boolean

awhere = maintenance when I highlight the above line.

I did not write this code - am just trying to fix

Norie

Does this work?

With ActiveWorkbook.Worksheets(Awhere)
     .Visible = True
     Application.Goto .Range("B3")
End With

Open in new window

leezac

ASKER

imnorie - I think should work but does not.

And when I manually try to change it properties window --
When I try to select the dropdown manually to unhide for the Visible property - I get "Unable to set the Visible property of the Worksheet class" error message

This happens to me on occasion and I can't remember what I do to fix.

Norie

Is the sheet/workbook protected?

leezac

ASKER

I had the person using the file open a prior one and it works so just may use it. Seems like a bug. The file was working fine with same code for a while. This just happened.

Norie

Are there any other workbooks open when you get the error?

leezac

ASKER

No - I closed the other workbooks.

ASKER CERTIFIED SOLUTION

andrew_man

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial