In Excel 2003, I have a dropdown list (created using Data/Validation and
referring to cells in the same worksheet). Depending on the selection that's
made from that list, I'd like the cursor/focus to jump a different cell or
area on the same sheet. There are nine items on the list and the cursor
would go to a different area or cell for each one. For example, if the user
selects "Widget A" from the list, the cursor would move to the first cell in
a series of questions that are specific to Widget A, and so on for each
widget. Is this possible, and how would I do it?

Thanks-
Karen

Karen, it is most definitely possible, and relatively painless to get it
done. I've written the code below so that hopefully it will be easy for you
to understand and to change it to meet your needs. There is even one example
of how to use it to jump to a cell on another sheet.

You will need to change things like cell addresses and the one sheet name
mentioned (or delete that line of code if you don't need to go to another
sheet). Hopefully the whole thing will serve as a template for your own code.

To place the code into your workbook, choose the sheet with your validated
list on it and right-click on the sheet's name tab and choose [View Code]
from the list that pops up. Then copy the code below and paste it into the
code module that appears. Edit the code and give it a try.

Here's the code, hope it helps you out.

Private Sub Worksheet_Change(ByVal Target As Range)
'this must be the address of the cell
'with data validation and you must
'include the $ symbol before the
'column ID and row number in it.
If Target.Address < "$A$1" Then
'did not select a new list entry
Exit Sub
End If
'here we test the contents of the
'list cell and go to another location
'based on its contents
'we will assume your source list for
'the validation list is in cells
'J1 through J9 and compare the
'item selected to each of them so
'that if you ever change a list
'entry you don't have to edit
'this code.
Select Case Target.Value
Case Is = Range("J1").Value
Range("B1").Select ' go to cell B1
Case Is = Range("J2").Value
Range("D9").Select ' got to cell D9
Case Is = Range("J3").Value
'we are going to a different sheet
'have to activate that sheet and
'then select the cell we need to get to
Worksheets("Sheet2").Activate
Range("A5").Select ' on Sheet2
Case Is = Range("J4")
Range("B1").Select ' go to cell B1
Case Is = Range("J5")
Range("G12").Select ' go to cell G12
Case Is = Range("J6")
Range("F4").Select ' go to cell F4
Case Is = Range("J7")
Range("E3").Select ' go to cell E3
Case Is = Range("J8")
Range("H4").Select ' go to cell H4
Case Is = Range("J9")
Range("B9").Select ' go to cell B9
Case Else
'don't know what to do
'so don't do anything
'just leave this section empty
End Select
End Sub


"Karen Sigel" wrote:

In Excel 2003, I have a dropdown list (created using Data/Validation and
referring to cells in the same worksheet). Depending on the selection that's
made from that list, I'd like the cursor/focus to jump a different cell or
area on the same sheet. There are nine items on the list and the cursor
would go to a different area or cell for each one. For example, if the user
selects "Widget A" from the list, the cursor would move to the first cell in
a series of questions that are specific to Widget A, and so on for each
widget. Is this possible, and how would I do it?

Thanks-
Karen

Thank you so much! This works perfectly! (And I never would have gotten
there on my own!)

Karen

"JLatham" wrote:

Karen, it is most definitely possible, and relatively painless to get it
done. I've written the code below so that hopefully it will be easy for you
to understand and to change it to meet your needs. There is even one example
of how to use it to jump to a cell on another sheet.

You will need to change things like cell addresses and the one sheet name
mentioned (or delete that line of code if you don't need to go to another
sheet). Hopefully the whole thing will serve as a template for your own code.

To place the code into your workbook, choose the sheet with your validated
list on it and right-click on the sheet's name tab and choose [View Code]
from the list that pops up. Then copy the code below and paste it into the
code module that appears. Edit the code and give it a try.

Here's the code, hope it helps you out.

Private Sub Worksheet_Change(ByVal Target As Range)
'this must be the address of the cell
'with data validation and you must
'include the $ symbol before the
'column ID and row number in it.
If Target.Address < "$A$1" Then
'did not select a new list entry
Exit Sub
End If
'here we test the contents of the
'list cell and go to another location
'based on its contents
'we will assume your source list for
'the validation list is in cells
'J1 through J9 and compare the
'item selected to each of them so
'that if you ever change a list
'entry you don't have to edit
'this code.
Select Case Target.Value
Case Is = Range("J1").Value
Range("B1").Select ' go to cell B1
Case Is = Range("J2").Value
Range("D9").Select ' got to cell D9
Case Is = Range("J3").Value
'we are going to a different sheet
'have to activate that sheet and
'then select the cell we need to get to
Worksheets("Sheet2").Activate
Range("A5").Select ' on Sheet2
Case Is = Range("J4")
Range("B1").Select ' go to cell B1
Case Is = Range("J5")
Range("G12").Select ' go to cell G12
Case Is = Range("J6")
Range("F4").Select ' go to cell F4
Case Is = Range("J7")
Range("E3").Select ' go to cell E3
Case Is = Range("J8")
Range("H4").Select ' go to cell H4
Case Is = Range("J9")
Range("B9").Select ' go to cell B9
Case Else
'don't know what to do
'so don't do anything
'just leave this section empty
End Select
End Sub


"Karen Sigel" wrote:

In Excel 2003, I have a dropdown list (created using Data/Validation and
referring to cells in the same worksheet). Depending on the selection that's
made from that list, I'd like the cursor/focus to jump a different cell or
area on the same sheet. There are nine items on the list and the cursor
would go to a different area or cell for each one. For example, if the user
selects "Widget A" from the list, the cursor would move to the first cell in
a series of questions that are specific to Widget A, and so on for each
widget. Is this possible, and how would I do it?

Thanks-
Karen

Glad I could help, and thank you for the feedback.

"Karen Sigel" wrote:

Thank you so much! This works perfectly! (And I never would have gotten
there on my own!)

Karen

"JLatham" wrote:

Karen, it is most definitely possible, and relatively painless to get it
done. I've written the code below so that hopefully it will be easy for you
to understand and to change it to meet your needs. There is even one example
of how to use it to jump to a cell on another sheet.

You will need to change things like cell addresses and the one sheet name
mentioned (or delete that line of code if you don't need to go to another
sheet). Hopefully the whole thing will serve as a template for your own code.

To place the code into your workbook, choose the sheet with your validated
list on it and right-click on the sheet's name tab and choose [View Code]
from the list that pops up. Then copy the code below and paste it into the
code module that appears. Edit the code and give it a try.

Here's the code, hope it helps you out.

Private Sub Worksheet_Change(ByVal Target As Range)
'this must be the address of the cell
'with data validation and you must
'include the $ symbol before the
'column ID and row number in it.
If Target.Address < "$A$1" Then
'did not select a new list entry
Exit Sub
End If
'here we test the contents of the
'list cell and go to another location
'based on its contents
'we will assume your source list for
'the validation list is in cells
'J1 through J9 and compare the
'item selected to each of them so
'that if you ever change a list
'entry you don't have to edit
'this code.
Select Case Target.Value
Case Is = Range("J1").Value
Range("B1").Select ' go to cell B1
Case Is = Range("J2").Value
Range("D9").Select ' got to cell D9
Case Is = Range("J3").Value
'we are going to a different sheet
'have to activate that sheet and
'then select the cell we need to get to
Worksheets("Sheet2").Activate
Range("A5").Select ' on Sheet2
Case Is = Range("J4")
Range("B1").Select ' go to cell B1
Case Is = Range("J5")
Range("G12").Select ' go to cell G12
Case Is = Range("J6")
Range("F4").Select ' go to cell F4
Case Is = Range("J7")
Range("E3").Select ' go to cell E3
Case Is = Range("J8")
Range("H4").Select ' go to cell H4
Case Is = Range("J9")
Range("B9").Select ' go to cell B9
Case Else
'don't know what to do
'so don't do anything
'just leave this section empty
End Select
End Sub


"Karen Sigel" wrote:

In Excel 2003, I have a dropdown list (created using Data/Validation and
referring to cells in the same worksheet). Depending on the selection that's
made from that list, I'd like the cursor/focus to jump a different cell or
area on the same sheet. There are nine items on the list and the cursor
would go to a different area or cell for each one. For example, if the user
selects "Widget A" from the list, the cursor would move to the first cell in
a series of questions that are specific to Widget A, and so on for each
widget. Is this possible, and how would I do it?

Thanks-
Karen