Texbox values based on Combobox Value(more detail)

T

Todd Huttenstine

Im sorry but I dont have any code for this so far, however
I do have code at the bottom of this post for when I click
the 3 Option Buttons and ComboBox1 auto-populates with
data from the corresponding columns on Sheet2 Range A2:C52.


I have a userform that contains TextBox1, TextBox2,
TextBox3, OptionButton1, OptionButton2, OptionButton3, and
ComboBox1.

I have a table of data on Sheet2 Range A2:C52.

When I select an option button, it auto-populates
ComboBox1 with the corresponding data in the columns in
Range A2:C52.
OptionButton1 is corresponding with data in columnA,
OptionButton2 is corresponding with data in columnB, and
OptionButton3 is corresponding with data in columnC.

Now, I need a code that will match the value in ComboBox1
with a value in Range A2:C52 on Sheet2. If a match is NOT
found, then do nothing(this will never happen because
anything that is in ComboBox1 will be found in the
range). If there IS a match with the value in ComboBox1
(which there always will be), then I need for the code to
put the corresponding data in the corresponding
TextBoxes. The way it will do this is as follows:

If the match is in a cell in Column A, then I need it to
put the data in that cell in Column A into TextBox1, and
then the corresponding data in the other 2 cells in the
columns into the corresponding 2 Textboxes.

If the match is in a cell in Column B, then I need it to
put the data in that cell in Column B into TextBox2 and
then the corresponding data in the other 2 cells in the
columns into the corresponding 2 Textboxes.

If the match is in a cell in Column C, then I need it to
put the data in that cell in Column C into TextBox3 and
then the corresponding data in the other 2 cells in the
columns into the corresponding 2 Textboxes.

Here is some info that may help...
Below is the code I am using for when I click the 3 Option
Buttons and ComboBox1 auto-populates with data from the
corresponding columns on Sheet2 Range A2:C52...

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

Thank you

Todd Huttenstine
 
T

Tom Ogilvy

Note that setting ListIndex to 0 in your option button code, that the user
won't be able to select the first item in the list.


if combobox1.Listindex <> -1 then
With worksheets("Sheet2")
for each cell in .Range("A2:C52")
if cell.Value = 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

or
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.Value = 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
 
T

Todd Huttenstine

where do I put that code?
-----Original Message-----
Note that setting ListIndex to 0 in your option button code, that the user
won't be able to select the first item in the list.


if combobox1.Listindex <> -1 then
With worksheets("Sheet2")
for each cell in .Range("A2:C52")
if cell.Value = 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

or
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.Value = 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


--
Regards,
Tom Ogilvy





.
Click to expand...
 
T

Tom Ogilvy

I guess where you want the action to happen. I would probably look at the
combobox1_click event, but I don't know your application. You might want to
update the textboxes by pressing a commandbutton in which case it would go
in the click event of the command button.
 
T

Todd Huttenstine

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

Tom Ogilvy

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

Tom Ogilvy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

list box remove item by combobox 4
Dependant ComboBox on a UserForm 1
Word macro that can call cells out of Excel for a find and replace 0
VBA Coding Help for Beginner 0
Need Help with a VBA subroutine 0
ComboBox value matching 0
Combobox date value matching 0
Run time 9 error for some users but not everyone 10

Top