Combo Box help!
 

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

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

Combo Box help!
 

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/

Combo Box help!
 

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...


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