Hide all sheets except one with VBA

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Hide all sheets except one with VBA

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    112
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide all sheets except one with VBA

     
    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

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,484
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide all sheets except one with VBA

    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
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Posts
    112
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide all sheets except one with VBA

    Thank you so much worked great!

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,484
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide all sheets except one with VBA

    Glad to help, thanks for the feedback.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    Board Regular
    Join Date
    Jan 2015
    Posts
    112
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide all sheets except one with VBA

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com