CODEWITHSUNDEEP
excelvba
NCC
NCC

Reputation: 819

Hide and unhide selected sheets in Excel using VBA

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:

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.

Upvotes: 1

Views: 9843

Answers (1)

NCC
NCC

Reputation: 819

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

Upvotes: 2

Related Questions