go to specific cell using drop down box

[amelia]

Is it possible to actually go to a specific cell when the user select it from
the drop down box? (this specific cell is in the input range of the drop down
box). Im using MS 2007.

For instance: Input range of the drop down box contains Cat, Rabbit and
Lion. When the user selects Lion from the drop down, it will go to cell A5
which contains the word Lion.(where cell A1-Cat, A3-Rabbit, A5-Lion)

Please advice if this situation is possible. Many thanks.

 

[Don Guillett]

Right click sheet tab>view code>copy/paste this.
do a data validation LIST with dog, cat,fish.
It wouldn't matter where the desired animal lived.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$1" Then Exit Sub
Cells.Find(What:=Target, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
End Sub

 

[Rick Rothstein]

Since you know the column that the list is in, I would think restricting the
search to that column would be better (then there is no chance of finding
the same search word in another cell). Maybe like this...

Range("A:A").Find(What:="Lion", After:=Cells(Rows.Count, "A"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Activate

 

[Don Guillett]

Rick,
Why are we looking in columns instead of rows?

 

[Rick Rothstein]

Well, actually, since Range("A:A") restricts the search to only Column A, we actually don't care what the search order is; so, in reality, that argument doesn't really need to be specified at all. However, my reasoning at the time for using xlByColumns is that I wanted the search to be down the columns rather than across the rows because the data was in all in a single column.