Run-time error ‘1004’

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
I’ve a sheet with bunch of form based buttons and purpose is each time you click a button it displays a sheet or group of sheets and when you click on another button all those previously opened sheet/s gets hidden. For some reason, the same code below worked just fine in my previous spreadsheet but not working when I am trying to reuse the code for new requirements where I have different sheet names. In this case, I have already changed the sheet names. The error I get is: “Run-time error ‘1004’: Select method of worksheet class failed.” And, when I click on Debug button, it highlights in Yellow color to the following line within my code: ‘Sheets(sh).Select’. If you can please take a look at my code and provide suggestions, I will appreciate that. Thanks in advance.

Code below:

Sub Which_Button()
Dim szMsg As String, szTitle As String, szAnswer As String

szMsg = "Keep the other Sheet(s) open?"
szTitle = "Previous Sheet(s)"
Select Case Application.Caller
Case "MAIN": Sheets("MAIN").Select

Case "MAIN2": Sheets("MAIN2").Select

'DTR Compliance
Case "DTR Compliance":
MySheets = Array("Solution Design", "Monthly Status")
Call Hide_Show(MySheets)

Case "DTR Results":
MySheets = Array("Solution Design", "Monthly Status", "Org - SPL Region", "By Org2009")
Call Hide_Show(MySheets)
szAnswer = MsgBox(szMsg, vbYesNo, szTitle)
If szAnswer = vbNo Then
Open_Sheets = Array("By Org2009")
Sheets(Open_Sheets).Visible = False
End If


Case "DTR":
MySheets = Array("Solution Design", "DTR Compliance")
Call Hide_Show(MySheets)


Case "HIDEALL":
For Each wsSheet In Worksheets
If wsSheet.Name = "Solution Design" Or wsSheet.Name = "Solution Delivery" Then
Else
wsSheet.Visible = False
End If
Next wsSheet

Case Else
End Select
End Sub

Function Hide_Show(MySheets)
Application.ScreenUpdating = False
For Each ws In Sheets
x = Application.Match(ws.Name, MySheets, 0)
If Not IsError(x) Then
ws.Visible = True
Else
ws.Visible = False
End If
Next ws
Application.ScreenUpdating = True
sh = Application.Caller
Sheets(sh).Select
End Function
Sub Show_All()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
For Each wsSheet In Worksheets
wsSheet.Visible = True
Next wsSheet
Application.ScreenUpdating = True
End Sub


Thank you very much!!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Thank you for your response. Well, they are hidden but the purpose is when you click on a button code should open up those sheet/s.
 
Upvote 0
I have a separate sheet and I have named that "Solution Design" and that sheet has about 9 buttons. Each buttons are assigned to specific sheets. Sheet "Solution Design" never gets hidden. There is a piece of code (that I provided) that keeps sheet "Solution Design" always visible. I hope this explains. But, if you have some better solution please suggest. I am open to redo the work that works. Thanks again!
 
Upvote 0
What's the name of the button? It should be the same as the sheet you want to select. To check its name you can use:

MsgBox Application.Caller

To change it right click, overwrite the name in the Name box and press Enter.
 
Upvote 0

Forum statistics

Threads
1,207,197
Messages
6,077,016
Members
446,250
Latest member
Dontcomehereoften

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top