User Form Help

[robertyguy]

Hi,

can any one help me please ?

What I'm trying do to use combo box on a form I have created which
will display data in column 'A' e.g. Zip codes

When the user has found the Zip code they require in Column A, and
pressed the OK button the associated data in columns B, C & D will be
displayed in the Text box on the form

e.g. the user uses the combo box is used to select data in A12

Then press the OK Button

The data in B12, C12, D12 is displayed in the text box


Any assistance would be appreciated


Many thanks


Rob

NB Excel version 2000

 

[Sharad Naik]

You can do Find, within colum A, for the selected data, and get the row
number. With row number known, you can then display the data in same row for
columns B, C and D in the text box.

Sharad

 

[Tom Ogilvy]

Private Sub Userform_Initialize()
Dim rng as Range
With Worksheets("Sheet1")
set rng = .Range(.Cells(2,1),.Cells(2,1).end(xldown))
End With
Combobox1.RowSource = rng.Address(External:=True)
End sub

Private Sub Combobox1_Click()
Dim rng as Range
With Combobox1
set rng = Range(.Rowsource)
Textbox1.Text = rng(.ListIndex + 1,2)
Textbox2.Text = rng(.ListIndex + 1,3)
Textbox3.Text = rng(.ListIndex + 1,4)
End With
End Sub

No reason to use a commandbutton since you can have the selection trigger
the populating of the textboxes. If you want a command button, then put the
code in the Combobox1_click event in the CommandButton1_Click event.