Filling Textboxes from Combobox selection
 

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

Filling Textboxes from Combobox selection
 

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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

tom:

think you have an extra end with in there.

--


Gary


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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

"Tom Ogilvy" wrote in message ...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

tom:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

it is a reference to the worksheet that contains the range

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
tom:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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

--


Gary


"Tom Ogilvy" wrote in message
...
it is a reference to the worksheet that contains the range

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
tom:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
so are
ws.rng
and
rng.parent
the same?

--


Gary


"Tom Ogilvy" wrote in message
...
it is a reference to the worksheet that contains the range

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
tom:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

ok, thanks

--


Gary


"Tom Ogilvy" wrote in message
...
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




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
so are
ws.rng
and
rng.parent
the same?

--


Gary


"Tom Ogilvy" wrote in message
...
it is a reference to the worksheet that contains the range

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
tom:

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

--


Gary


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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

"Tom Ogilvy" wrote in message ...
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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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

--
Regards,
Tom Ogilvy



"Corey" wrote in message
...
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....

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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


"Tom Ogilvy" wrote in message ...
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

--
Regards,
Tom Ogilvy



"Corey" wrote in message
...
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....

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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



--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Corey" wrote in message
...
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....

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....

Filling Textboxes from Combobox selection
 

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

Thank You again for your assistance.

Corey....
"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"Corey" wrote in message
...
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....

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
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....