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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> SAP PoD
> ------------------------------------------------------------------------
> SAP PoD's Profile:
http://www.excelforum.com/member.php...o&userid=37641
> View this thread: http://www.excelforum.com/showthread...hreadid=572552
>

[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)

"SAP PoD" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> SAP PoD
> ------------------------------------------------------------------------
> SAP PoD's Profile:
http://www.excelforum.com/member.php...o&userid=37641
> View this thread: http://www.excelforum.com/showthread...hreadid=572552
>

[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

On Thu, 17 Aug 2006 14:07:18 +0100, "Bob Phillips" <[email protected]>
wrote:

>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)
>
>"SAP PoD" <[email protected]> wrote in
>message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> SAP PoD

[SAP PoD]

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

This works exactly as I wanted.