| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Event Procedure - Pick List Choice Unhides Worksheet
Hello,
I have a "Master Worksheet" in which there is a cell that allows the user to choose different geographic regions from a drop list (using data validation). In addition, there are 10 hidden worksheets I will call "supporting" (one for each geographic region). I would like to create an event procedure that unhides the supporting worksheet associated with the geographic region chosen from the Master Worksheet, and hides (or keeps hidden) the other other supporting worksheets. More succinctly, only show the Master worksheet and the associated supporting worksheet as dictated by that chosen in the Master...... Thanks a lot in advance! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Event Procedure - Pick List Choice Unhides Worksheet
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Region As String Region = Sheet1.Cells(2, 1).Value Select Case Region Case "North" Sheets("North").Visible = True Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False Case "South" Sheets("North").Visible = False Sheets("South").Visible = True Sheets("East").Visible = False Sheets("West").Visible = False Case "East" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = True Sheets("West").Visible = False Case "West" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = True Case Else Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False End Select End Sub "JEFF" wrote: Hello, I have a "Master Worksheet" in which there is a cell that allows the user to choose different geographic regions from a drop list (using data validation). In addition, there are 10 hidden worksheets I will call "supporting" (one for each geographic region). I would like to create an event procedure that unhides the supporting worksheet associated with the geographic region chosen from the Master Worksheet, and hides (or keeps hidden) the other other supporting worksheets. More succinctly, only show the Master worksheet and the associated supporting worksheet as dictated by that chosen in the Master...... Thanks a lot in advance! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Event Procedure - Pick List Choice Unhides Worksheet
Hi David,
Set it up an example that put my master as the first sheet, and I created four supporting worksheets named North, South, East, and West. I pasted you code into the "Sheet1" module... No luck. Feeling a little stupid..... "David Hepner" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim Region As String Region = Sheet1.Cells(2, 1).Value Select Case Region Case "North" Sheets("North").Visible = True Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False Case "South" Sheets("North").Visible = False Sheets("South").Visible = True Sheets("East").Visible = False Sheets("West").Visible = False Case "East" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = True Sheets("West").Visible = False Case "West" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = True Case Else Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False End Select End Sub "JEFF" wrote: Hello, I have a "Master Worksheet" in which there is a cell that allows the user to choose different geographic regions from a drop list (using data validation). In addition, there are 10 hidden worksheets I will call "supporting" (one for each geographic region). I would like to create an event procedure that unhides the supporting worksheet associated with the geographic region chosen from the Master Worksheet, and hides (or keeps hidden) the other other supporting worksheets. More succinctly, only show the Master worksheet and the associated supporting worksheet as dictated by that chosen in the Master...... Thanks a lot in advance! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Event Procedure - Pick List Choice Unhides Worksheet
Post it in "ThisWorkbook" not sheet1 and I should mention that the data
validation is in cell A2. "JEFF" wrote: Hi David, Set it up an example that put my master as the first sheet, and I created four supporting worksheets named North, South, East, and West. I pasted you code into the "Sheet1" module... No luck. Feeling a little stupid..... "David Hepner" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim Region As String Region = Sheet1.Cells(2, 1).Value Select Case Region Case "North" Sheets("North").Visible = True Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False Case "South" Sheets("North").Visible = False Sheets("South").Visible = True Sheets("East").Visible = False Sheets("West").Visible = False Case "East" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = True Sheets("West").Visible = False Case "West" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = True Case Else Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False End Select End Sub "JEFF" wrote: Hello, I have a "Master Worksheet" in which there is a cell that allows the user to choose different geographic regions from a drop list (using data validation). In addition, there are 10 hidden worksheets I will call "supporting" (one for each geographic region). I would like to create an event procedure that unhides the supporting worksheet associated with the geographic region chosen from the Master Worksheet, and hides (or keeps hidden) the other other supporting worksheets. More succinctly, only show the Master worksheet and the associated supporting worksheet as dictated by that chosen in the Master...... Thanks a lot in advance! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Event Procedure - Pick List Choice Unhides Worksheet
There we go! If it is not asking too much, what is the difference between
creating a new module and using "ThisWorkbook"? Thanks again! "David Hepner" wrote: Post it in "ThisWorkbook" not sheet1 and I should mention that the data validation is in cell A2. "JEFF" wrote: Hi David, Set it up an example that put my master as the first sheet, and I created four supporting worksheets named North, South, East, and West. I pasted you code into the "Sheet1" module... No luck. Feeling a little stupid..... "David Hepner" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim Region As String Region = Sheet1.Cells(2, 1).Value Select Case Region Case "North" Sheets("North").Visible = True Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False Case "South" Sheets("North").Visible = False Sheets("South").Visible = True Sheets("East").Visible = False Sheets("West").Visible = False Case "East" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = True Sheets("West").Visible = False Case "West" Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = True Case Else Sheets("North").Visible = False Sheets("South").Visible = False Sheets("East").Visible = False Sheets("West").Visible = False End Select End Sub "JEFF" wrote: Hello, I have a "Master Worksheet" in which there is a cell that allows the user to choose different geographic regions from a drop list (using data validation). In addition, there are 10 hidden worksheets I will call "supporting" (one for each geographic region). I would like to create an event procedure that unhides the supporting worksheet associated with the geographic region chosen from the Master Worksheet, and hides (or keeps hidden) the other other supporting worksheets. More succinctly, only show the Master worksheet and the associated supporting worksheet as dictated by that chosen in the Master...... Thanks a lot in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation Procedure with a worksheet change event | Excel Worksheet Functions | |||
| Start Macro after user selects a choice from a pick list | Excel Discussion (Misc queries) | |||
| Event Procedure - Data Validation List | Excel Programming | |||
| Calculate Value of Cell From Pick List Choice | Excel Discussion (Misc queries) | |||
| Using VBA to track changes in a worksheet - help! Event procedure?? | Excel Programming | |||
Linear Mode
