Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Call up worksheet based on validated list
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Call up worksheet based on validated list
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Call up worksheet based on validated list
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Call up worksheet based on validated list
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Call up worksheet based on validated list
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" 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Call up worksheet based on validated list
Thanks to both of you - Gord, thanks for clearing up the confusion. This works exactly as I wanted. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto updating list | Excel Worksheet Functions | |||
Populating worksheet via a drop down list ! | Excel Worksheet Functions | |||
Search a worksheet, extract rows using a list from another sheet | Excel Discussion (Misc queries) | |||
Fill automatically from one worksheet to another based on cel valu | Excel Discussion (Misc queries) | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) |