Combo Box help!

K

kkhan

Hi,
This maybe simple, but I'm new to VB.
1) Ok, I want to make up a combo box which consists of cell value
B4:B48. Any column can be empty sometimes therefore, the combo bo
should be populated only with non-null values.

2) Also, when the user click on any one of the items from the comb
box, the values of their corresponding rows should be displayed i
their respective textboxs which are already placed on the user form.
For example, if the item on the combo list is 'Soda' (B5) then it
corresponding textboxs for rows 'Close' (C5) & 'Open' (D5) shoul
display its value in the textbox automatically.

Sorry for this mess, I hope you understand what i'm trying to acheiv
:)

- Kkha
 
T

Tom Ogilvy

Private Sub Userform_Initialize()
Dim cell as Range
Combobox1.rowsource = ""
with worksheets("Sheet1")
for each cell in .Range("B4:B48")
if len(trim(cell.text)) > 0 then
Combobox1.AddItem cell.Value
end if
Next
End With

Private Sub Combobox1_Click()
Dim res as Variant
Dim rng as Range, rng1 as Range
with worksheets("Sheet1")
set rng = .Range("B4:B48")
End With
res = Application.Match(Combobox1.Value,rng,0)
if not iserror(res) then
set rng1 = rng(res)
Textbox1.Text = rng1.offset(0,1).Value
Textbox2.Text = rng1.offset(0,2).Value
Else
Textbox1.Text = ""
Textbox2.Text = ""
end if
End Sub
 
K

kkhan

can't thank you enough for this tom...i've one more question for
anyone.....
its simple..
how do u get add that command button which pops up when the workbook
opens? its this command button that triggers the macro...anyone know
what i'm talking about ?
thanks in advance...
 
Top