go to specific cell using drop down box

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.
 
D

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
 
R

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
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top