Combo Box help!

  • Thread starter Thread starter kkhan
  • Start date Start date
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
 
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
 
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...
 
Back
Top