Filling Textboxes from Combobox selection

[Corey]

When i select value from a list in a Combobox, how do i fill some textboxes with other values
from the same row as the selected Combobox value.
EG.
If A10 was the selected value for the Combobox
How do i do this :
IE.
Textbox1.value = Combobox1.Offset(0,5).Value ? ' (A15)??

Corey....

 

[Tom Ogilvy]

dim rng as Range, idex as Long, rw as Long
With Combobox1
set rng = Range(.RowSource).Columns(1).Cells
End with
idex = .ListIndex + 1
End with
rw = rng(idex).row
Textbox1.Value = rng.parent.Cells(rw,6).Text
Textbox2.Value = rng.parent.Cells(rw,7).Text

 

[Gary Keramidas]

tom:

think you have an extra end with in there.

 

[Corey]

Thank you Tom.
Am i best to place this in the Combobox Exit event?

dim rng as Range, idex as Long, rw as Long
With Combobox1
set rng = Range(.RowSource).Columns(1).Cells
End with
idex = .ListIndex + 1
End with
rw = rng(idex).row
Textbox1.Value = rng.parent.Cells(rw,6).Text
Textbox2.Value = rng.parent.Cells(rw,7).Text

 

[Tom Ogilvy]

Personally, I would use the click event - and you should probably restrict
your combobox to accept only selections from the list

Private Sub ComboBox1_Click()
Dim rng As Range, idex As Long, rw As Long
With Me
With .ComboBox1
Set rng = Range(.RowSource).Columns(1).Cells
idex = .ListIndex + 1
End With
End With
rw = rng(idex).Row
Textbox1.Value = rng.Parent.Cells(rw, 6).Text
Textbox2.Value = rng.Parent.Cells(rw, 7).Text
End Sub

this corrects some typos as well.

 

[Gary Keramidas]

tom:

i've seen rng.parent a couple of times today. can you explain what it's used
for?

 

[Tom Ogilvy]

it is a reference to the worksheet that contains the range

 

[Gary Keramidas]

so are
ws.rng
and
rng.parent
the same?

 

[Tom Ogilvy]

no. ws.rng would raise an error since there is no rng property of a
worksheet.


a range reference is a specific range on a specific worksheet in a specific
workbook

set rng = Activesheet.range("B9")
? rng.Address(0,0,xlA1,True)
'[Book1]Trip missed'!B9


rng.parent is the worksheet

rng.parent.parent is the workbook

? typename(rng.parent)
Worksheet
? typename(rng.parent.parent)
Workbook

 

[Gary Keramidas]

ok, thanks

--


Gary

no. ws.rng would raise an error since there is no rng property of a
worksheet.


a range reference is a specific range on a specific worksheet in a specific
workbook

set rng = Activesheet.range("B9")
? rng.Address(0,0,xlA1,True)
'[Book1]Trip missed'!B9


rng.parent is the worksheet

rng.parent.parent is the workbook

? typename(rng.parent)
Worksheet
? typename(rng.parent.parent)
Workbook


--
Regards,
Tom Ogilvy

so are
ws.rng
and
rng.parent
the same?

 

[Corey]

Tom,
I am getting a 'Method Range of Object _Global Failed' error on this line highlightd line ?:

Private Sub ComboBox2_Click()
Dim rng As Range, idex As Long, rw As Long
With Me
With .ComboBox2
Set rng = Range(.RowSource).Columns(1).Cells ' <=== here ??
idex = .ListIndex + 1
End With
End With
rw = rng(idex).Row
TextBox3.Value = rng.Parent.Cells(rw, 1).Value
TextBox4.Value = rng.Parent.Cells(rw, 7).Value
End Sub

I checked the Combobox BoundColumn and it it set to 1.

Corey....

Personally, I would use the click event - and you should probably restrict
your combobox to accept only selections from the list

Private Sub ComboBox1_Click()
Dim rng As Range, idex As Long, rw As Long
With Me
With .ComboBox1
Set rng = Range(.RowSource).Columns(1).Cells
idex = .ListIndex + 1
End With
End With
rw = rng(idex).Row
Textbox1.Value = rng.Parent.Cells(rw, 6).Text
Textbox2.Value = rng.Parent.Cells(rw, 7).Text
End Sub

this corrects some typos as well.

 

[Tom Ogilvy]

What does your rowsource look like? Mine looks like this:

? userform2.ComboBox1.RowSource
Sheet1!A1:A10


The code works perfectly for me, over and over. Everytime I click to select
a value from the dropdown, textbox1 and textbox 2 fill with the appropriate
values whether Sheet1 is the activesheet or not. (code copied right out of
my post).

PS. If you are not filling your combobox by using the RowSource property,
then obviously this approach would not work - but I am sure you would know
that. In that case, you would have to have some knowledge of where to look
for your data and use a similar approach.

here is the code again:

Private Sub ComboBox1_Click()
Dim Rng As Range, idex As Long, rw As Long
With Me
With .ComboBox1
Set Rng = Range(.RowSource).Columns(1).Cells
idex = .ListIndex + 1
End With
End With
rw = Rng(idex).Row
TextBox1.Value = Rng.Parent.Cells(rw, 6).Text
TextBox2.Value = Rng.Parent.Cells(rw, 7).Text
End Sub

 

[Corey]

I would still rate myself a beginner at code, but
was thinking that it was the way i had the Combobox list populated that was causing the problem.

I populate it with code not RowSource.

The code i use is:

Private Sub ComboBox2_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox2.ListCount > 0 Then Exit Sub
Dim LastCell As Long
Dim myrow As Long
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To LastCell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And .Cells(myrow,
1).Offset(0, 0).Value = ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox2.AddItem Cells(myrow, 3).Offset(i, 0)
ComboBox2.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address
' <======= This is where i thought i could ADD the other Textbox Values Offset from what i selected.
End If
Next i
End If
End If
Next
End With

Application.ScreenUpdating = True
End Sub


What does your rowsource look like? Mine looks like this:

? userform2.ComboBox1.RowSource
Sheet1!A1:A10


The code works perfectly for me, over and over. Everytime I click to select
a value from the dropdown, textbox1 and textbox 2 fill with the appropriate
values whether Sheet1 is the activesheet or not. (code copied right out of
my post).

PS. If you are not filling your combobox by using the RowSource property,
then obviously this approach would not work - but I am sure you would know
that. In that case, you would have to have some knowledge of where to look
for your data and use a similar approach.

here is the code again:

Private Sub ComboBox1_Click()
Dim Rng As Range, idex As Long, rw As Long
With Me
With .ComboBox1
Set Rng = Range(.RowSource).Columns(1).Cells
idex = .ListIndex + 1
End With
End With
rw = Rng(idex).Row
TextBox1.Value = Rng.Parent.Cells(rw, 6).Text
TextBox2.Value = Rng.Parent.Cells(rw, 7).Text
End Sub

 

[Tom Ogilvy]

So you can get the information you need right where you put it when you were
adding the information:

ComboBox2.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 3).Offset(i,
0).Address

so:
Private Sub Combobox2_Click()
Dim s as String, rng as Range
s = Combobox2.List(combobox2.ListIndex,1)
Worksheets("InspectionData")
set rng = .Cells(range(s).Row,"A")
TextBox1.Value = .Cells(rng.row, 6).Text
TextBox2.Value = .Cells(rng.row, 7).Text
End With
End Sub

 

[Corey]

Thank You Tom.
You code was perfect.
Finally I have been able to complete my project now.

Thank You again for your assistance.

Corey....