[Excel] Hiding/Unhiding sheets using an User Form

'************************************************************
' The code to call the Form should be written in Modules Section
'************************************************************
Sub HideUnhideSelectedSheets()
UserFormHideUnhide.Show
End Sub

' The Code to generate form should be written inside Forms section as shown below:
' Create a Form named "UserFormHideUnhide"

'************************************************************
'*** Code behind UserFormHideUnhide form
' - The form displays Visible and Invisible sheets
' side-by-side. The users can use the buttons to switch
' sheets between Hidden and Visible options
' - Once the user submits the form all the changes are
' applied to the sheets' visibility
'*** Names of the Controls on the UserForm
' Listbox: ListBox1 - displays a list of visible sheets
' Listbox: ListBox2 - displays a list of hidden sheets
' Button: MoveAllToRight - ">>" used to make all sheets
' but one hidden
' Button: MoveSelectedToRight - ">" hide one sheet at a time
' Button: MoveSelectedToLeft - "<" unhide one sheet at a time ' Button: MoveAllToLeft - ">>" used to make all sheets
' visible
' Button: Cancel - used to cancel the Form(dialog box)
' Button: Hide/Unhide - used to submit the UserForm
'************************************************************

'*** Initialize dialog box with Visible and Hidden sheets list

Private Sub UserForm_Initialize()

Dim sht As Variant
Dim shts As Sheets

For Each sht In ActiveWorkbook.Sheets
If sht.Visible Then
ListBox1.AddItem sht.Name
Else: ListBox2.AddItem sht.Name
End If
Next sht

End Sub

'*** Button to Hide all Visible sheets
Private Sub MoveAllToRight_Click()
'Add all but one Visible sheets into Hidden sheets list
For i = 1 To ListBox1.ListCount - 1
ListBox2.AddItem ListBox1.List(i)
Next i
'Remove all but one sheets from Visible sheets list
For i = 1 To ListBox1.ListCount - 1
ListBox1.RemoveItem (1)
Next i

MsgBox "At least one sheet should be visible"

End Sub

'***Button to hide selected visible sheets
Private Sub MoveSelectedToRight_Click()

Dim CountVisibleSheets, LastSelection, j As Integer
CountVisibleSheets = ListBox1.ListCount - 1

'Excel requires at least one sheet to be visible
If ListBox1.ListCount = 1 Then
LastSelection = 0
MsgBox "At least one sheet should be visible"
Else
'Add selected sheets to Hidden sheets listbox
For i = 0 To CountVisibleSheets
If ListBox1.Selected(i) Then
ListBox2.AddItem ListBox1.List(i)
End If
Next i

'Remove selected sheets from Visible sheets list
j = 0
Do Until j = ListBox1.ListCount
If ListBox1.Selected(j) Then
ListBox1.RemoveItem (j)
LastSelection = j
j = j - 1
End If
j = j + 1
Loop
End If

'Maintain a selection by moving selection to next visible sheet
If LastSelection < ListBox1.ListCount Then
ListBox1.Selected(LastSelection) = True
Else
ListBox1.Selected(LastSelection - 1) = True
End If

End Sub

'*** Button to Unhide selected Hidden sheets
Private Sub MoveSelectedToLeft_Click()
Dim CountVisibleSheets, LastSelection As Integer
CountVisibleSheets = ListBox2.ListCount - 1

For i = 0 To CountVisibleSheets
If ListBox2.Selected(i) Then
ListBox1.AddItem ListBox2.List(i)
End If
Next i

j = 0
Do Until j = ListBox2.ListCount
If ListBox2.Selected(j) Then
ListBox2.RemoveItem (j)
LastSelection = j
j = j - 1
End If
j = j + 1
Loop
'Maintain a selection by moving selection to next Hidden sheet
If LastSelection < ListBox2.ListCount Then
ListBox2.Selected(LastSelection) = True
ElseIf ListBox2.ListCount = 0 Then
Else
ListBox2.Selected(LastSelection - 1) = True
End If
End Sub

'*** Button to Unhide all Hidden sheets
Private Sub MoveAllToLeft_Click()
Dim CountHiddenSheets As Integer
CountHiddenSheets = ListBox2.ListCount - 1

For i = 0 To CountHiddenSheets
ListBox1.AddItem ListBox2.List(i)
Next i

For i = 0 To CountHiddenSheets
ListBox2.RemoveItem (0)
Next i
End Sub

'*** Cancel dialog button
Private Sub CommandCancel_Click()
Unload UserFormHideUnhide
End Sub

'*** Submit selection for hiding and unhiding sheets
Private Sub CommandHideUnhide_Click()
For i = 0 To ListBox1.ListCount - 1
ActiveWorkbook.Sheets(ListBox1.List(i)).Visible = True
Next i
For i = 0 To ListBox2.ListCount - 1
If i = ActiveWorkbook.Sheets.Count - 1 Then
MsgBox "At least one sheet should be visible"
Exit For
Else
ActiveWorkbook.Sheets(ListBox2.List(i)).Visible = False
End If
Next i
End Sub