User Form Help

R

robertguy

Hi,

can any one help me please ?

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

When the user has found the Zip code they require in Column A, an
pressed the OK button the associated data in columns B, C & D will b
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 200
 
D

Dave Peterson

I created a userform with a combobox, 3 textboxes and a commandbutton.

I put this code behind the userform:

Option Explicit
Dim myRng As Range
Private Sub ComboBox1_Change()
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
End Sub
Private Sub CommandButton1_Click()
Dim myIndex As Long
myIndex = Me.ComboBox1.ListIndex
If myIndex = -1 Then
'do nothing, nothing's selected
Else
With myRng.Cells(1)
Me.TextBox1.Value = .Offset(myIndex, 1).Value
Me.TextBox2.Value = .Offset(myIndex, 2).Value
Me.TextBox3.Value = .Offset(myIndex, 3).Value
End With
End If
End Sub
Private Sub UserForm_Initialize()
With Worksheets("Sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With Me.ComboBox1
.Style = fmStyleDropDownList
.List = myRng.Value
End With
End Sub
 
Top