Macros to Hide some worksheets in a workbook

[vicmoment]

I have a workbook with 10 worksheets. The 10 worksheets belong to 5 diff industries. e.g Sheet 1 & 2 - Technology, Sheet3&4 - oil and Gas, Sheet 5 and 6 - pharmaceutical.....


What i want to do is have a listbox pop up when the workbook is opened that asks which industry you want and after a selection is made, only the worksheets belonging to that industry are made visible while the others are hidden.


Thanks


Victor

 

[vaskov17]

Not sure if the code below helps but it's one way of doing it. In the Workbook_Open event put a call to HideSheets to run the macro when the workbook is opened.


Sub HideSheets()

Dim choice As Integer

Dim strList As String

Dim ws As Worksheet


Do Until choice > 0 And choice < 6

choice = InputBox("Select industry:" & vbCrLf & "1 - Industry 1" & vbCrLf & _

"2 - Industry 2" & vbCrLf & "3 - Industry 3" & vbCrLf & "4 - Industry 4" & vbCrLf & _

"5 - Industry 5")

Loop


Select Case choice

Case 1: strList = "|Sheet1|Sheet2|"

Case 2: strList = "|Sheet3|Sheet4|"

Case 3: strList = "|Sheet5|Sheet6|"

Case 4: strList = "|Sheet7|Sheet8|"

Case 5: strList = "|Sheet9|Sheet10|"

End Select


For Each ws In ThisWorkbook.Worksheets

If InStr(1, LCase(strList), "|" & LCase(ws.Name) & "|", vbTextCompare) Then

ws.Visible = xlSheetVisible

Else

ws.Visible = xlSheetHidden

End If

Next ws

End Sub