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

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

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

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

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

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

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

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

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

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

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