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!

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!

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!

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!

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!