Results 1 to 5 of 5

Hide all sheets except one with VBA

This is a discussion on Hide all sheets except one with VBA within the Excel Questions forums, part of the Question Forums category; I have 20 sheets in the workbook and want to hide 19 of them with a click of a button. ...

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    111

    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
    42,556

    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
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    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
    111

    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
    42,556

    Default Re: Hide all sheets except one with VBA

    Glad to help, thanks for the feedback.
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    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
    111

    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

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