Combo Box Search

I am new to vb programing in excel and I want to create a userform with combox list.
What I want to accomplish is when a user choses an item in the combo box it will go to that cell.

For example: In the combobox I have listed 3 choices: chevy, ford and dodge.

In my spread sheet I have column "A" as Manufacturer in which the first 80rows has "ford" (a10:a80)in the next 80 rows it has Chevy(a81:a161) and in the next 80 dodge.

I have created my user form with combo box displaying "chevy, ford and dodge" and I have a command button.

When the user selects for example "ford" it would then scroll down to the first row with the word ford in column "A".

I have gotten the user form and the combo box figured out but I am stuck from there on.

Can Anyone help???????????
Please...........................

Here is the code I have for the user form.

---------------------------------
Private sub combobox_1_change()
combobox1.dropdown
end sub

Private sub commandbutton_1_click()

end sub


Private sub userform_initialize()
combobox1.additems."ford"
combobox1.additems."chevy"
combobox1.additems."dodge"
end sub

Private sub combobox1_Click()
Dim rng as Range, rng1 as Range
If combobox1.ListIndex <> -1 then
With worksheets("Data")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
set rng1 = rng.Find(What:=Combobox1.Value, _
After:=rng(rng.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng1 is nothing then
Application.Goto reference:=rng1, _
Scroll:=True
Else
msgbox Combobox1.Value & " Not found"
End if
End If
end sub

This triggers when the selection is made. If you want to only do it when
the commandbutton is clicked, then put it in the Click event of the command
Button

Private Sub CommandButton1_Click()
Dim rng as Range, rng1 as Range
If combobox1.ListIndex <> -1 then
With worksheets("Data")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
set rng1 = rng.Find(What:=Combobox1.Value, _
After:=rng(rng.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng1 is nothing then
Application.Goto reference:=rng1, _
Scroll:=True
Else
msgbox Combobox1.Value & " Not found"
End if
End If
End Sub


--
Regards,
Tom Ogilvy

"tssgw" <[email protected]> wrote in
message news:[email protected]...
>
> I am new to vb programing in excel and I want to create a userform with
> combox list.
> What I want to accomplish is when a user choses an item in the combo
> box it will go to that cell.
>
> For example: In the combobox I have listed 3 choices: chevy, ford and
> dodge.
>
> In my spread sheet I have column "A" as Manufacturer in which the first
> 80rows has "ford" (a10:a80)in the next 80 rows it has Chevy(a81:a161)
> and in the next 80 dodge.
>
> I have created my user form with combo box displaying "chevy, ford and
> dodge" and I have a command button.
>
> When the user selects for example "ford" it would then scroll down to
> the first row with the word ford in column "A".
>
> I have gotten the user form and the combo box figured out but I am
> stuck from there on.
>
> Can Anyone help???????????
> Please...........................
>
> Here is the code I have for the user form.
>
> ---------------------------------
> Private sub combobox_1_change()
> combobox1.dropdown
> end sub
>
> Private sub commandbutton_1_click()
>
> end sub
>
>
> Private sub userform_initialize()
> combobox1.additems."ford"
> combobox1.additems."chevy"
> combobox1.additems."dodge"
> end sub
>
>
> --
> tssgw
> ------------------------------------------------------------------------
> tssgw's Profile:
http://www.excelforum.com/member.php...o&userid=35121
> View this thread: http://www.excelforum.com/showthread...hreadid=548760
>

thanks it works great