Friday, November 20, 2009

[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

8 comments:

  1. This works nicely but with one problem. Say I have 20 sheets in a workbook, sheets 3-20 are all hidden and listed in listbox2. I want to make sheet 17 visible but when I select sheet 17 and move it over, sheets 18-20 are then removed from listbox2 along with it. They are not visible in either listbox. Then if I were to move sheet 10 to listbox1, sheets 11-16 then are no longer there. Is there any way to fix this, it would make it much more user friendly?

    ReplyDelete
    Replies
    1. Steve you can use below codes

      '***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)
      ListBox1.RemoveItem (i)
      End If
      Next i
      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)
      ListBox2.RemoveItem (i)
      End If
      Next i
      End Sub

      Delete
  2. Hi

    You have to set MultiSelect for each listbox to frmMultiSelectMulti under Properties. Very nice piece of code this.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. This comment has been removed by the author.

      Delete
  4. I got an error when trying to move a single item from left to right or vice versa. It hangs at the following lines:

    "If ListBox2.Selected(i) Then"
    or
    "If ListBox1.Selected(i) Then"

    Error message:
    "Run-time error '2147024809 (80070057)':
    Could not get the Selected property. Invalid argument."

    To fix this I changed the For loops to step backwards, replacing:

    For i = 0 to CountVisibleSheets
    With :
    For i = CountVisibleSheets To 0 Step -1

    There are two of these lines, one for the move left and one for the move right.

    Also, to preserve the order of the sheets in the list after you move them, add:

    ListBox1.Clear
    ListBox2.Clear

    Before the code in the UserForm_Initialize Sub, and call the sub at the end of the CommandHideUnhide Sub.

    Works a treat!

    ReplyDelete
  5. Hi

    This code is very cool, and thanks for your efforts and sharing with us. I have one request, could you please let me know how to have the sheets to be very hidden instead of just not visible?

    Thanks
    Cameron

    ReplyDelete