go to specific cell using drop down box

  • Thread starter Thread starter amelia
  • Start date Start date
A

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.
 
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
 
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
 
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.
 
Back
Top