Reputation: 383
I have a ListBox (embedded in a sheet) that (upon clicking a button to refresh as needed) will fill with the names of all the sheets in the workbook. (Each has a checkbox to go with it.)
Code:
Private Sub CommandButton1_Click()
Dim i As Long
Me.ListBox1.Clear
For i = 1 To Sheets.Count
Me.ListBox1.AddItem Sheets(i).Name
Next
End Sub
I have tried some various options, but cant sort out a solution as to how to unhide and hide the sheets if they are checked and unchecked, respectively. (And to check/uncheck them if a user manually unhides or hides sheets the old fashioned way.)
Any help is appreciated. Thank you!
Upvotes: 0
Views: 1288
Reputation: 12497
Try this:
Private Sub CommandButton1_Click()
Dim i As Long
Me.ListBox1.Clear
For i = 1 To Worksheets.count
Me.ListBox1.AddItem Worksheets(i).Name
If Worksheets(i).Visible Then
Me.ListBox1.Selected(i - 1) = True
End If
Next
End Sub
Private Sub ListBox1_Change()
Dim i As Integer
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
Worksheets(Me.ListBox1.List(i)).Visible = True
Else
Worksheets(Me.ListBox1.List(i)).Visible = False
End If
Next i
End Sub
CommandButtton1
click will populate listbox with sheet names and checkboxListBox1_Change
will hide/unhide sheets as per checkbox valueUpvotes: 1