Returning Values
 

Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1) and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes are
stored in multiple columns on a sheet within the workbook. (Sheet1) (Columns
A,B,C,D) What I would like to do is this: When a user selects a value from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like the
user to be able to change it in the Textbox and have the values automatically
update on the sheet. Any help would really be appreciated.

Returning Values
 

Hi,
You can set that up through the Properties without a single line of code:

Say you data for the combo is in sheet1!A2:A10
1- in the userform, select the combo and set its properties:
- fill up the combo with values. Set RowSource to : sheet1!A2:A10
- send the choice index to another sheet (hidden)
set BoundColumn to: 0 --- so that .Value returns the index
set ControlSource to: sheet2!a1 -- send Value there
Now the combo reads sheet1!a2:a10 and when the user pick a choice , it
sends the index of the choice (starting at 1) to sheet2!a1
2- Make textboxes read the corresponding value
- Texbox1 - set its ControlSOurce property to:
offset(sheet1!a2,sheet2!a1-1,1,1,1)
ie from begining of data (sheet1!a2) go 'sheet2!a1-1' row down, and 1
col right.
- Textbox2- set its ControlSOurce property to:
offset(sheet1!a2,sheet2!a1-1,2,1,1)
ie from begining of data (sheet1!a2) go 'sheet2!a1-1' row down, and 2
col right.
- ...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Eric" wrote:

Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1) and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes are
stored in multiple columns on a sheet within the workbook. (Sheet1) (Columns
A,B,C,D) What I would like to do is this: When a user selects a value from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like the
user to be able to change it in the Textbox and have the values automatically
update on the sheet. Any help would really be appreciated.

Returning Values
 

Hello Eric,

I am currently dealing with a similar problem. If the comments left in
this tread are not helpful to you, than perhaps taking a look at what
Tom has suggested to me will be of some use.

See:
http://www.excelforum.com/showthread.php?t=513312

I am trying to use an index/match formula.

See ya',
Amber :)


Eric Wrote:
Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1)
and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes
are
stored in multiple columns on a sheet within the workbook. (Sheet1)
(Columns
A,B,C,D) What I would like to do is this: When a user selects a value
from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like
the
user to be able to change it in the Textbox and have the values
automatically
update on the sheet. Any help would really be appreciated.


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=513328

Returning Values
 

Eric,
For Part 1:

Private Sub ComboBox1_Change()

Set ws1 = Worksheets("Sheet1") '<=== change as required
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " not found"
Else
For i = 1 To 3 ' populate textboxes 1 to 3
Me.Controls("textbox" & i) = .Cells(res, i + 1)
Next i
End If
End With
End Sub


For Part 2: do you have a command button to "submit" the text box updates?
If so, then code should go in the Commandbutton_Click macro.

Something like:

private sub commandbutton1_click

Set ws1 = Worksheets("Sheet1") '<=== change as required
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " not found"
Else
For i = 1 To 3 ' populate cells with textboxes 1 to 3
If Me.Controls("textbox" & i) < "" then .Cells(res, i +
1)=Me.Controls("textbox" & i).value
Next i
End If
End With
End sub

HTH

"Eric" wrote:

Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1) and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes are
stored in multiple columns on a sheet within the workbook. (Sheet1) (Columns
A,B,C,D) What I would like to do is this: When a user selects a value from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like the
user to be able to change it in the Textbox and have the values automatically
update on the sheet. Any help would really be appreciated.

Returning Values
 

Thanks Amber. I am going to look at your article too. The formula returns the
values okay, but if I change one of the values in a textbox, it still does
not update the sheet where the values are stored.

I changed the RowSource property of my ComboBox to 'Sheet1'!$A:$D and then
placed the following code in the Change event for the ComboBox:

Me.TextBox1.Value = Me.ComboBox1.Column(1)
Me.TextBox2.Value = Me.ComboBox1.Column(2)
Me.TextBox3.Value = Me.ComboBox1.Column(3)

Thanks again and I'll check out your article.
Eric

"Amber_D_Laws" wrote:


Hello Eric,

I am currently dealing with a similar problem. If the comments left in
this tread are not helpful to you, than perhaps taking a look at what
Tom has suggested to me will be of some use.

See:
http://www.excelforum.com/showthread.php?t=513312

I am trying to use an index/match formula.

See ya',
Amber :)


Eric Wrote:
Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1)
and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes
are
stored in multiple columns on a sheet within the workbook. (Sheet1)
(Columns
A,B,C,D) What I would like to do is this: When a user selects a value
from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like
the
user to be able to change it in the Textbox and have the values
automatically
update on the sheet. Any help would really be appreciated.


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=513328

Returning Values
 

Okay, I added a command button and put in the codes you to but the only value
that is being updated is the value in Column "B". Here's the code I have:

Private Sub ComboBox1_Change()

Dim res As Variant
Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
Me.Controls("TextBox" & i) = .Cells(res, i + 1)
Next i
End If
End With
End Sub

Private Sub CommandButton1_Click()

Dim res As Variant

Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
If Me.Controls("TextBox" & i) < "" Then .Cells(res, i + 1) =
Me.Controls _ ("TextBox" & i).Value
Next i
End If
End With
End Sub

The values in Textboxes 3 & 4 are not updating with new info. They just
redisplay the data that was already there. Do you see where my mistake is?
Thanks again!


"Toppers" wrote:

Eric,
For Part 1:

Private Sub ComboBox1_Change()

Set ws1 = Worksheets("Sheet1") '<=== change as required
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " not found"
Else
For i = 1 To 3 ' populate textboxes 1 to 3
Me.Controls("textbox" & i) = .Cells(res, i + 1)
Next i
End If
End With
End Sub


For Part 2: do you have a command button to "submit" the text box updates?
If so, then code should go in the Commandbutton_Click macro.

Something like:

private sub commandbutton1_click

Set ws1 = Worksheets("Sheet1") '<=== change as required
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " not found"
Else
For i = 1 To 3 ' populate cells with textboxes 1 to 3
If Me.Controls("textbox" & i) < "" then .Cells(res, i +
1)=Me.Controls("textbox" & i).value
Next i
End If
End With
End sub

HTH

"Eric" wrote:

Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1) and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes are
stored in multiple columns on a sheet within the workbook. (Sheet1) (Columns
A,B,C,D) What I would like to do is this: When a user selects a value from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like the
user to be able to change it in the Textbox and have the values automatically
update on the sheet. Any help would really be appreciated.

Returning Values
 

Okay, I added a command button and put in the codes your article told me to
but the only value
that is being updated is the value in TextBox1. Here's the code I have:

Private Sub ComboBox1_Change()

Dim res As Variant
Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
Me.Controls("TextBox" & i) = .Cells(res, i + 1)
Next i
End If
End With
End Sub

Private Sub CommandButton1_Click()

Dim res As Variant

Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
If Me.Controls("TextBox" & i) < "" Then .Cells(res, i + 1) =
Me.Controls _ ("TextBox" & i).Value
Next i
End If
End With
End Sub

The values in Textboxes 2 & 3 are not updating with new info. They just
redisplay the data that was already there. Do you see where my mistake is?
Thanks again!


"Eric" wrote:

Thanks Amber. I am going to look at your article too. The formula returns the
values okay, but if I change one of the values in a textbox, it still does
not update the sheet where the values are stored.

I changed the RowSource property of my ComboBox to 'Sheet1'!$A:$D and then
placed the following code in the Change event for the ComboBox:

Me.TextBox1.Value = Me.ComboBox1.Column(1)
Me.TextBox2.Value = Me.ComboBox1.Column(2)
Me.TextBox3.Value = Me.ComboBox1.Column(3)

Thanks again and I'll check out your article.
Eric

"Amber_D_Laws" wrote:


Hello Eric,

I am currently dealing with a similar problem. If the comments left in
this tread are not helpful to you, than perhaps taking a look at what
Tom has suggested to me will be of some use.

See:
http://www.excelforum.com/showthread.php?t=513312

I am trying to use an index/match formula.

See ya',
Amber :)


Eric Wrote:
Hello there, I have a UserForm on which there is 1 ComboBox (ComboBox1)
and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and the TextBoxes
are
stored in multiple columns on a sheet within the workbook. (Sheet1)
(Columns
A,B,C,D) What I would like to do is this: When a user selects a value
from
ComboBox1 (Gathered from Column A), I would like the Textboxes to
automatically fill in based upon the values from Columns B,C, and D
respectively. That is part one.

Part two, If the information gathered needs to be changed, I would like
the
user to be able to change it in the Textbox and have the values
automatically
update on the sheet. Any help would really be appreciated.


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=513328

Returning Values
 

Well I am glad that the information others is providing me is bein
helpful to you too. I wish I could offer some more advice, but as I a
certainly no expert, sadly I can not. Toppers is really good at all o
this, so hopefully with his help you two can get it sorted out.

Good Luck,
Amber :)


Eric Wrote:
Okay, I added a command button and put in the codes your article told m
to
but the only value
that is being updated is the value in TextBox1. Here's the code
have:

Private Sub ComboBox1_Change()

Dim res As Variant
Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
Me.Controls("TextBox" & i) = .Cells(res, i + 1)
Next i
End If
End With
End Sub

Private Sub CommandButton1_Click()

Dim res As Variant

Set ws1 = Worksheets("Customer Database")
ws1.Activate
With ws1
res = Application.Match(ComboBox1.Value, Range("A:A"), 0)
If IsError(res) Then
MsgBox ComboBox1.Value & " Not Found"
Else
For i = 1 To 3
If Me.Controls("TextBox" & i) < "" Then .Cells(res, i + 1) =
Me.Controls _ ("TextBox" & i).Value
Next i
End If
End With
End Sub

The values in Textboxes 2 & 3 are not updating with new info. The
just
redisplay the data that was already there. Do you see where my mistak
is?
Thanks again!


"Eric" wrote:

Thanks Amber. I am going to look at your article too. The formul
returns the
values okay, but if I change one of the values in a textbox, it stil
does
not update the sheet where the values are stored.

I changed the RowSource property of my ComboBox to 'Sheet1'!$A:$D an
then
placed the following code in the Change event for the ComboBox:

Me.TextBox1.Value = Me.ComboBox1.Column(1)
Me.TextBox2.Value = Me.ComboBox1.Column(2)
Me.TextBox3.Value = Me.ComboBox1.Column(3)

Thanks again and I'll check out your article.
Eric

"Amber_D_Laws" wrote:


Hello Eric,

I am currently dealing with a similar problem. If the comments lef
in
this tread are not helpful to you, than perhaps taking a look a
what
Tom has suggested to me will be of some use.

See:
http://www.excelforum.com/showthread.php?t=513312

I am trying to use an index/match formula.

See ya',
Amber :)


Eric Wrote:
Hello there, I have a UserForm on which there is 1 ComboBo
(ComboBox1)
and 3
TextBoxes (Textboxes 1,2,3). The values for ComboBox1 and th
TextBoxes
are
stored in multiple columns on a sheet within the workbook
(Sheet1)
(Columns
A,B,C,D) What I would like to do is this: When a user selects
value
from
ComboBox1 (Gathered from Column A), I would like the Textboxe
to
automatically fill in based upon the values from Columns B,C, an
D
respectively. That is part one.

Part two, If the information gathered needs to be changed,
would like
the
user to be able to change it in the Textbox and have the values
automatically
update on the sheet. Any help would really be appreciated.


--
Amber_D_Laws

------------------------------------------------------------------------
Amber_D_Laws's Profile
http://www.excelforum.com/member.php...o&userid=30012
View this thread
http://www.excelforum.com/showthread...hreadid=513328



--
Amber_D_Law
-----------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001
View this thread: http://www.excelforum.com/showthread.php?threadid=51332