Hide Certain Sheets based on Cell Data
 

I have a multiple sheet workbook in which certain items are assigned a code
of from a to g. Each of those items then has 2 other sheets associated with
it. How can i make a macro look up the code result, and then close all of the
sheets not needed.

For example, if the result is A, sheets 1 and 7 need to be open, B sheets 2
and 8, etc. There are also some sheets that stay open no matter what.

Thanks for any help.

Bill

Hide Certain Sheets based on Cell Data
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arySheets
Dim nVisible As Long
Dim i As Long
Dim sh As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
Select Case .Value
Case "A": arySheets = Array("Sheet1", "Sheet2")
Case "B": arySheets = Array("Sheet1", "Sheet3")
Case "C": arySheets = Array("Sheet1", "Sheet4")
End Select
End With
End If

For Each sh In ThisWorkbook.Worksheets
nVisible = xlSheetHidden
For i = LBound(arySheets) To UBound(arySheets)
If sh.Name = arySheets(i) Then
nVisible = xlSheetVisible
Exit For
End If
Next i
sh.Visible = nVisible
Next sh

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

"Volsfan" wrote in message
...
I have a multiple sheet workbook in which certain items are assigned a
code
of from a to g. Each of those items then has 2 other sheets associated
with
it. How can i make a macro look up the code result, and then close all of
the
sheets not needed.

For example, if the result is A, sheets 1 and 7 need to be open, B sheets
2
and 8, etc. There are also some sheets that stay open no matter what.

Thanks for any help.

Bill

Hide Certain Sheets based on Cell Data
 

Bob,

That code was almost exactly what I needed. However, I have one problem. The
cell that I am using as the value cell gets it result from a formula. In
other words, I have a list of Numbers in a Combo Box, once a number is
selected, a lookup table is used to attribute a letter to that number and
that letter is returned in the selected cell. This method does not result in
this code working. If I manually change the cell to whatever case I desire,
works like a charm, but the value changing as a result of the lookup does not
make the code run, I guess. Any further suggestions, short of making a case
for each of the number values??

Thanks for the help.

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arySheets
Dim nVisible As Long
Dim i As Long
Dim sh As Worksheet
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
Select Case .Value
Case "A": arySheets = Array("Sheet1", "Sheet2")
Case "B": arySheets = Array("Sheet1", "Sheet3")
Case "C": arySheets = Array("Sheet1", "Sheet4")
End Select
End With
End If

For Each sh In ThisWorkbook.Worksheets
nVisible = xlSheetHidden
For i = LBound(arySheets) To UBound(arySheets)
If sh.Name = arySheets(i) Then
nVisible = xlSheetVisible
Exit For
End If
Next i
sh.Visible = nVisible
Next sh

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

"Volsfan" wrote in message
...
I have a multiple sheet workbook in which certain items are assigned a
code
of from a to g. Each of those items then has 2 other sheets associated
with
it. How can i make a macro look up the code result, and then close all of
the
sheets not needed.

For example, if the result is A, sheets 1 and 7 need to be open, B sheets
2
and 8, etc. There are also some sheets that stay open no matter what.

Thanks for any help.

Bill