Hide all sheets except one with VBA

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
122
I have 20 sheets in the workbook and want to hide 19 of them with a click of a button.

Below code works but only hides the active sheet and not the other 18. Is there a better way to go about this?

Private Sub CommandButton1_Click()
With Sheets("Main Menu")
.Visible = True
.Activate
End With


Me.Visible = False

End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

Code:
Private Sub CommandButton1_Click()
Sheets("Main Menu").Visible = True

For Each ws In WorkSheets
    If ws.Name <> "Main Menu" Then ws.Visible = False
Next ws
End Sub
 

Dokat

Board Regular
Joined
Jan 19, 2015
Messages
122
Now I can't get buttons to work and receiving "Can't Exit Design Mode because Control 'CommandButton1' can not be created" Error. Do you know what may cause this problem and how to fix it?

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,095,533
Messages
5,445,042
Members
405,307
Latest member
TINA31415

This Week's Hot Topics

Top