Returning Values

[Guest]

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.

 

[Guest]

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

 

[Amber_D_Laws]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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!

 

[Amber_D_Laws]

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