Call up worksheet based on validated list

[SAP PoD]

Hi - I did search the forum, but could not see an answer.

Can I make Excel open a specific worksheet based on the users selection
from a Validation List?

So, user has option A,B,C. If they choose 'A' it straight away opens
up worksheet 'A' - If they choose 'B' it straight away opens up
worksheet 'B' and so on.

Hope I explained the requirement OK.

PoD

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Workbooks.Open .Value
End With
End If

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

(replace somewhere in email address with gmail if mailing direct)

 

[SAP PoD]

Hi,

Sorry, but I am a fairly basic Excel user and never copied code before.
I figured out where to put the code, but it does not seem to do
anything. Did I have to change some of the values in the code to
reflect my choices and the names of the worksheets I want them to go
to?

Sorry if that's a dumb question

 

[Bob Phillips]

It is based upon your data validation being in cell H10. Change that to
suit.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gord Dibben]

Not sure but there may some confusion on what OP wants.

I interpret OP's needs to be "select a worksheet" and not open a workbook.

Modified version is........

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Worksheets(Range("H10").Value).Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

If I have interpreted incorrectly, please place post in appropriate container.


Gord Dibben MS Excel MVP

 

[SAP PoD]

Thanks to both of you - Gord, thanks for clearing up the confusion.

This works exactly as I wanted.