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

--
Regards,
Tom Ogilvy




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

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

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

- Kkhan


---
Message posted from http://www.ExcelForum.com/