You stated in your specifications, "Range on Sheet2" as an example.

I used Sheet2 as the name of the worksheet in accordance with your
specification.

Looking back at your previous posts, Bob originally did the same, but you
later said you had to change it to use the index number of the sheet which
might indicate that your sheet isn't actually named Sheet2. I can't see
your sheet or read your mind. But maybe if you change Sheet2 to index 2, the
code might work for you. Seems that not describing your situation
correctly was a problem the last time as well.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I recommended the combobox click event - the change event would fire on
every keystroke if you allow the user to hand enter the data instead of
picking from the list. Nonetheless, the code works fine in either for me.

I copied both pieces of code from the email and pasted them into the
combobox1 click event and both pieces of code worked exactly as intended.
They populated the textboxes with the appropriate values from the source
range. (also tested with the change event - no problem)

No errors were encounted. .

the only modification I would make is for column 1, if it contains numbers
instead of text, the comparison fails because text is being compared to a
number - so the box doesn't get populated.

There is no sense rewriting your description and reposting Bob's code.
The
code provided does exactly what you described.

Your time would be better invested in figuring out how to paste code into
your module without screwing it up and developing some proficiency in
understanding the code, then learning how to modify it if doesn't do
exactly
what you want.

Here is the second piece of code, copied out of a working module:

Private Sub Combobox1_Click()

If ComboBox1.ListIndex < -1 Then
With Worksheets("sheet2")
If OptionButton1.Value Then
Set rng = .Range("A2:A52")
ElseIf OptionButton2.Value Then
Set rng = .Range("B2:B52")
Else
Set rng = .Range("C2:C52")
End If
For Each cell In rng
If cell.Text = ComboBox1.Value Then
TextBox1.Value = .Cells(cell.Row, 1).Value
TextBox2.Value = .Cells(cell.Row, 2).Value
TextBox3.Value = .Cells(cell.Row, 3).Value
Exit For
End If
Next
End With
End If


End Sub

--------------------------

here is the original first piece, again copied out of a working module.

rivate Sub Combobox1_Click()

If ComboBox1.ListIndex < -1 Then
With Worksheets("Sheet2")
For Each cell In .Range("A2:C52")
If cell.Text = ComboBox1.Value Then
TextBox1.Value = .Cells(cell.Row, 1).Value
TextBox2.Value = .Cells(cell.Row, 2).Value
TextBox3.Value = .Cells(cell.Row, 3).Value
Exit For
End If
Next
End With
End If
End Sub

Both have had the comparison statement modified to use .Text instead of
.Value to handle the condition where the cell might contain a number - so
the comparison is string to string.

--
Regards,
Tom Ogilvy




"Todd Huttenstine" wrote in message
...
I am having errors with both of the codes. I tried the
ComboBox change event because I think thats really where I
need it, but I still got errors. In the first code
supplied, I am having the error on the "for each cell"
part, then for the second code supplied, Im having
problems with the "set rng" part of the code.

Let me try to explain the situation again, this time in
more detail in case it is confusing(sorry if this is
long)...

I have a UserForm that contains OptionButton1,
OptionButton2, OptionButton3, TextBox1, TextBox2,
TextBox3,and ComboBox1. I also have a table of data on
Sheet2 Range A2:C52 that the form will reference. The
table is comprised of 3 columns. In Range A3:A52, there
are Employee ID numbers. In Range B3:B52, there are Names.
And in Range C3:C52, there are Email Addresses.

Heres how the program flows:
ComboBox1 references the OptionButtons to see which
OptionButton is Bulleted. The OptionButton bulleted will
determine what set of data the ComboBox1 will display.
For example, Clicking Optionbutton1 makes ComboBox1 look
at column A in range A2:C52 on sheet2 and auto-populate
with that data. The combobox does not show any blank
cells if there are blank cells in the range.

Clicking Optionbutton2 makes ComboBox1 look at column B in
range A2:C52 on sheet2 and auto-populate with that data.
The combobox does not show any blank cells if there are
blank cells in the range.

Clicking Optionbutton3 makes ComboBox1 look at column C in
range A2:C52 on sheet2 and auto-populate with that data.
The combobox does not show any blank cells if there are
blank cells in the range.

More important Information:
When the UserForm is open, OptionButton1 will be
automatically bulleted. Because of this, ComboBox1 will
contain a Valid Value. The ComboBox will always be
populated with a valid value because one of the
OptionButtons will always be bulleted.

Currently here is the code I am using to do all of the
above (make the ComboBox1 list a different set of values
when I select a different OptionButton). The code works
perfectly.

____________________________________
Option Explicit

Dim cLastRow As Long
Dim i As Long

Private Sub OptionButton1_Click()
cLastRow = Worksheets(2).Cells(Rows.Count, "A").End
(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow + 1
If Cells(i, "A") < "" Then
.AddItem Worksheets(2).Cells(i -
1, "A").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton2_Click()
cLastRow = Worksheets(2).Cells(Rows.Count, "B").End
(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow + 1
If Cells(i, "B") < "" Then
.AddItem Worksheets(2).Cells(i -
1, "B").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub OptionButton3_Click()
cLastRow = Worksheets(2).Cells(Rows.Count, "C").End
(xlUp).Row
With ComboBox1
.Clear
For i = 3 To cLastRow + 1
If Cells(i, "C") < "" Then
.AddItem Worksheets(2).Cells(i -
1, "C").Value
End If
Next i
.ListIndex = 0
End With
End Sub

Private Sub UserForm_Activate()
OptionButton1.Value = True
End Sub
____________________________________


Now heres the part I do not have yet and that I cant get
to work....

If and when the value in the ComboBox1 changes, so will
the value in the 3 textBoxes. Heres why and how it needs
to change...
The Value of the ComboBox1 will always match ONE of the
values of a cell in the Range A2:C52 on Sheet2. Because
of this, the ComboBox1 will contain a Valid Value. There
will be a matching value(to the value in the ComboBox) in
the Range A2:C52. When the match is found, the code needs
to populate the 3 TextBoxes with the corresponding value
on the same row.

TextBox1 needs to pull the Employee ID from Column A in
the Range on Sheet2, TextBox2 needs to pull the Name from
Column B in the Range on Sheet2, and TextBox3 needs to
pull the Email Address from Column C in the Range on
Sheet2.