Hide and unhide selected sheets in Excel using VBA

Question

Problem: I would like to have the ability to hide and unhide selected (multiple) sheets by a form

Available Resource: There many available resources that show how to unhide and hide all sheets at one but not flexible

Explain:

enter image description here

Because sheet5 is hidden checkbox corresponds to Sheet5 is checked.

Logic/approach:

Get all of the sheets' name include hided ones and bind them to Labels, check if it is hide or unhide and bind it to checkbox.

Label and Checkbox are created automatically, lable and checkbox is somehow linked to each other so the program knows which sheets to hide and to unhide.

write the code to check whether a checkbox changes its status between selected/unselected.

Thank you for asking. I am trying to do it in VBA of excel. The winform is just for explaining purpose: [1]I have learned how to hide and unhide sheet. [2] I have trouble with automatic create label and checkbox — NCC
– NCC, Commented May 5, 2012 at 9:36
Do you want to create all those controls at runtime? Or do you want to have those controls beforehand and simply enable/disable them based on whether the sheet is visible or not? — Siddharth Rout
– Siddharth Rout, Commented May 5, 2012 at 9:41
I would like to have the list when I run the form/macro. [1]The Form Display All Sheets (There are possible of 300sheets at max). [2]If the sheet is unhide make the checkbox is selected, if it is hide make the checkbox is unselected. [3]If I click on the selected ones - unhide, if I click on the unselected - hide. — NCC
– NCC, Commented May 5, 2012 at 9:51
There are possible of 300sheets at max In that case I suggest using a listbox or a combobox. :) — Siddharth Rout
– Siddharth Rout, Commented May 5, 2012 at 10:19

TylerH · Accepted Answer · 2019-08-13 19:12:09Z

2

At last I am able to do the work. It is probably not good code, but it works.

enter image description here

Private Sub btListAllSheets_Click()

    With Me.ListBox1

    .Clear
    .ColumnHeads = True
    .ColumnCount = 2
    Dim status As String

    For i = 1 To Sheets.Count
        If Sheets(i).Visible = xlSheetHidden Then
           status = "Invisible"
        Else
           status = "Visible"
        End If

    ListBox1.AddItem (Sheets(i).Name)
    ListBox1.List(ListBox1.ListCount - 1, 1) = status

    Next i
    End With

End Sub

Private Sub bt_hideunhideselectedsheet_Click()

    Dim str As String
    str = Me.ListBox1.Column(1, Me.ListBox1.ListIndex)

    For Each Sh In ThisWorkbook.Worksheets

    If Sh.Name = Me.ListBox1.Value And str = "Visible" Then    
        Sh.Visible = False   
    ElseIf Sh.Name = Me.ListBox1.Value And str = "Invisible" Then    
        Sh.Visible = True
    End If

    Next Sh

End Sub

edited Aug 13, 2019 at 19:12

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered May 5, 2012 at 19:33

NCC

81912 gold badges26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hide and unhide selected sheets in Excel using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related