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

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" wrote in
message ...

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


--
tssgw
------------------------------------------------------------------------
tssgw's Profile: http://www.excelforum.com/member.php...o&userid=35121
View this thread: http://www.excelforum.com/showthread...hreadid=548760