Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to hide sheets based upon cell value | Excel Discussion (Misc queries) | |||
Hide or unhide sheets based on cell | Excel Discussion (Misc queries) | |||
Hide sheets based on Cell value | Excel Discussion (Misc queries) | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) | |||
cut & paste between sheets based on cell data | Excel Programming |