Need to show and hide named ranges based on cell value

[WannaBe]

I have a spreadsheet with 10 named ranges. It contains a cell that corresponds to one of the named ranges and will change, based on input by the user. I want to show the named range based on the value of that cell, and hide the other ranges. Then when the value changes, I want to show THAT named range and hide the others. How do I do this?
TIA

[WannaBe]

I have created 10 separate macros, one for each named range. Is there a way to call the correct code depending on the value of the cell H1? And have it call the correct sub when H1 changes?

[WannaBe]

I've added this to the Change declarations, but I get the error Sub or Function not defined. Forgive the newbie question, but how do I call the macro?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4:$B$499" Then
Select Case Target.Value
Case "PCNLK"
Call Macro 'Sub PCNLK'


Case "PNP"
Call Macro 'Sub PNP'


Case "ONP"
Call Macro 'Sub ONP'


Case "ODkNLK"
Call Macro 'Sub ODkNLK'


Case "ODkP"
Call Macro 'Sub ODkP'


Case "ONNLK"
Call Macro 'Sub ONNLK'


Case "PCP"
Call Macro 'Sub PCP'


Case "PDkNLK"
Call Macro 'Sub PDkNLK'


Case "PDkP"
Call Macro 'Sub PDkP'


Case "PNNLK"
Call Macro 'Sub PNNLK'
End Select
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

[Paul_Hossler]

I added CODE tags to your macro above. My sig has instructions and a link to our FAQs. Take a minute are read through

Not tested, but something like this might work. It's in the QC worksheet code module



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("B4:B499")) Is Nothing Then Exit Sub


Application.EnableEvents = False ' <<<<<<<<<< don't want to keep triggering this event

Select Case Target.Cells(1, 1).Value
Case "PCNLK"
Call PCNLK
Case "PNP"
Call PNP
Case "ONP"
Call ONP
Case "ODkNLK"
Call ODkNLK
Case "ODkP"
Call ODkP
Case "ONNLK"
Call ONNLK
Case "PCP"
Call PCP
Case "PDkNLK"
Call PDkNLK
Case "PDkP"
Call PDkP
Case "PNNLK"
Call PNNLK
End Select

Application.EnableEvents = True

End Sub

[Artik]

Look in the attachment. In this solution, G1: H1 cells are unnecessary, unless you use them for other purposes. You must correct the names of some ranges.

Artik

[WannaBe]

Thank you! This worked perfectly after I corrected the rangenames!