Show worksheets based on multiple selections from a Userlist

[justDave]

Hi,

I've not used an awful lot of VBA over the past couple of years but I am desperately trying to get back into it.

I have a quick, and possibly easy, question for you all. Is it possible for me to script via VBA for some hidden tabs to unhide based on a user's selection from a Userlist. For example I may have 21 worksheets, one of which is always viewable upon opening the sheet and contains a Userlist which allows multiple selections (which are also the same name as the other 20 tabs, ie sheet 2, sheet 3 etc etc). Based on the user's selection I would then like to be able to script a button to make the selected sheets Unhidden.

Many thanks in advance for any help you may be able to provide.

Dave

 

[justDave]

Sorry for the 'bump' but does anyone know of a way I could achieve this?

Cheers,

 

[apo]

Hi..

As far as i know.. you can't have a multiselect combo.. you will need to use a Listbox with Multiselect option enabled. I may be wrong.. but i don't see any Multiselect options on the Combobox control.

Try this..

Add a Listbox (ListBox1) and a CommandButton (CommandButton1) to sheet1.

1. Paste this code into your Workbook Open event..
It will Hide all Sheets except Sheet 1.

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim wsObj As Object
  For Each ws In Worksheets
    If ws.Name <> "Sheet1" Then ws.Visible = xlSheetHidden
  Next
  Sheets("Sheet1").ListBox1.Clear


For Each wsObj In Sheets
    If wsObj.Visible = xlSheetHidden Then
        Sheets("Sheet1").ListBox1.AddItem wsObj.Name
    ElseIf wsObj.Visible = xlSheetVeryHidden Then
        ListBox1.AddItem ws.Name & "*"
    End If
Next
End Sub

2. Paste this code into Sheet1

It will make it so the Listbox ONLY displays sheets that are Hidden.
It will Unhide any sheets you select after pressing the Command Button.
You can multiselect.

Private Sub CommandButton1_Click()
Dim I As Long
Dim J As Long
Dim UnhSh As String
Dim arrItems() As String
    ReDim arrItems(0 To ListBox1.ColumnCount - 1)
    For J = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(J) Then
            For I = 0 To ListBox1.ColumnCount - 1
                arrItems(I) = ListBox1.Column(I, J)
            Next I
            UnhSh = Join(arrItems, ",")
        End If
        If ListBox1.Selected(J) = True Then
        Worksheets(UnhSh).Visible = xlSheetVisible
        End If
    Next J
 Worksheets(UnhSh).Activate
 Sheets("Sheet1").Activate
End Sub




Private Sub Worksheet_Activate()
ListBox1.Clear
Dim ws As Object
For Each ws In Sheets
    If ws.Visible = xlSheetHidden Then
        ListBox1.AddItem ws.Name
    ElseIf ws.Visible = xlSheetVeryHidden Then
        ListBox1.AddItem ws.Name & "*"
    End If
Next
End Sub