Event Procedure - Pick List Choice Unhides Worksheet

[Guest]

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!

 

[Norman Jones]

Hi Jeff,

If your support sheets had names corresponding to the data validation list
items( e.g. "Region1", Region2" ...), you could try something like:

'=======================>.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("A1"))

On Error GoTo XIT

If Not rng Is Nothing Then
If Not IsEmpty(rng) Then
ThisWorkbook.Sheets(Range("A1").Value).Select
End If
End If
XIT:

End Sub

'<<=======================>

This is worksheet event code and should be pasted into the Master
worksheets's code module (not a standard module and not the workbook's
ThisWorkbook module):

*******************************************
Right-click the Master worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

 

[Guest]

Hi Norman,

Thanks for responding.... I created a simple example with a Master having a
picklist in A1 and four Region worksheets with the same names found in the
pick-list. I pasted the code in the Master worksheets's code module (not a
standard module) , and I created an Enable Events code module to be sure it
was working... Unfortunately, I'm unsuccessful... Can you help?

 

[Norman Jones]

Hi Jeff,

and I created an Enable Events code module

How did you do this:?

Did you not simply right-click the Master sheet's tab ann paste the
suggested code?

 

[Guest]

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

 

[Guest]

Hi,

I inserted a new Module and entered:

**********************************************
Sub EnableEvents()

Application.EnableEvents = True

End Sub
*****************************************************

 

[Norman Jones]

Hi Jeff,

What version of Excel are you using?

 

[Guest]

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.....

 

[Guest]

Post it in "ThisWorkbook" not sheet1 and I should mention that the data
validation is in cell A2.

 

[Guest]

There we go! If it is not asking too much, what is the difference between
creating a new module and using "ThisWorkbook"?

Thanks again!