• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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
0
leezac
Asked:
leezac
  • 5
  • 3
  • 2
  • +2
1 Solution
 
nutschCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
And the spelling should be an exact match. Any leading, trailing or extra space would make it fail.
0
 
leezacAuthor Commented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
nutschCommented:
So the AWhere string should be set to Maintenace, not Sheet08, is that what you have?
0
 
leezacAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
Does this work?
With ActiveWorkbook.Worksheets(Awhere)
     .Visible = True
     Application.Goto .Range("B3")
End With

Open in new window

0
 
leezacAuthor Commented:
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.
0
 
NorieVBA ExpertCommented:
Is the sheet/workbook protected?
0
 
leezacAuthor Commented:
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.
0
 
NorieVBA ExpertCommented:
Are there any other workbooks open when you get the error?
0
 
leezacAuthor Commented:
No - I closed the other workbooks.
0
 
andrew_manCommented:
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
End If
GoMenu = True
End Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now