Hide Tabs with Macro!

[audrey]

Hi,

I have a big macro challenge for my excel knowledge!

I have an excel sheet that contains 100-120 tabs. But not all tabs are necesarily to be used. So I want to give the flexibility to the user to hide some of those tabs with a macro on the cover sheet. (First sheet) so that he/she can scroll around the sheet easier and efficently!

My question is, which button do you recommend me to start with ? Listbox looks the most approprite one to me! what do you guys think ?

then next question should it be a form control or active box ? how do fill in that boxes ?

Regards,

Audrey

 

[audrey]

 

[audrey]

is there a way to hide a tab if column C is "You can hide it" ?????
​

 

[audrey]

by tab I mean worksheet! in an excel sheet

 

[audrey]

actually I want to this!

http://vbacentral.blogspot.com/2009/11/excel-hidingunhiding-sheets-using-user.html

 

[Datsmart]

Here is a Dialog Sheet Macro built from ideas that John Walkenbach put together to originally print select sheets:

Sub HideSheets()
'** John Walkenbach
'** www.j-walk.com
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
'    Application.ScreenUpdating = False

'** Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

'** UnHide all worksheets
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = True Then On Error Resume Next
            ws.Visible = True
    Next ws

    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

'** Add a temporary dialog sheet
    Set OrigSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

'** Add the checkboxes
    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
        'Skip empty sheets
'        If Application.CountA(CurrentSheet.Cells) <> 0 Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name
            TopPos = TopPos + 13
'        End If
    Next i

'** Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

'** Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
        (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Check Sheets to Hide"
    End With

'** Change tab order of OK and Cancel buttons
'** so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

'** Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    '=========================================
                    Worksheets(cb.Caption).Visible = False
                    '=========================================
                End If
            Next cb
        End If
    Else
        MsgBox "All worksheets are empty."
    End If

'** Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete
    Application.DisplayAlerts = True

'** Reactivate original sheet
    OrigSheet.Activate
End Sub

I like the way a Dialog sheet is built in the code, then removed after it is done.
I set the code up to start by un-hiding all sheets, then you get to chose which ones to hide.

I like the idea of the selection of sheet names in your linked page userform, I will be looking at that soon.

 

[audrey]

'************************************************************
'*** 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

I am using this and I love it! the only downside is.....

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?

 

[audrey]

I like the idea of the selection of sheet names in your linked page userform, I will be looking at that soon.

Hi, I used your code as well; it s brilliant idea!!! However I have couple of concerns in terms of user friendliness...

1. Instead of unhiding everything from scratch, it would be great if the macro remembers what has been selected. Considering more than 120 sheets! user will have to go over the same process over and over again to hide sheets.

2. I am getting this error screen in the process
http://i53.tinypic.com/1z222vo.jpg

3. is there way to list the sheet name check box dialog box horizontally rather than vertically ? I cant see the rest of the list of sheets...

 

[Datsmart]

I am using this and I love it! the only downside is.....

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?

Did you set the Listboxes MultiSelect Property to selection "1 - fmMultiSelectMulti"?

 

[audrey]

By defaulty it was single.