Deleting/Adding/Changing values based on ComboBox1 Value

T

Todd Huttenstine

On a userform I have combobox1, Textbox1, Textbox2, and
Textbox3 and commandbutton1. The values of the 3
textboxes depend on the value of combobox1. Values of
combobox1 are pulled from range M2:M100. The user will
select a value from the combobox. When he/she selects a
value from the combobox, the textboxes will populate with
corresponding values from 3 different ranges. Textbox1
pulls in data from range P2:p100. Textbox2 pulls in data
from range Q2:Q100. Textbox3 pulls in data from range
R2:R100.

The user can then add/change/delete values in the 3
textboxes. When the user makes the desired changes,
he/she hits the Update commandbutton1. I need a code that
will make the changes to the appropriate corresponding
cell in the corresponding range that the textbox
references.


Thank you

Todd
 
J

J.E. McGimpsey

How are the textboxes populated to begin with?

Assuming that they're based on something like the Combobox ListIndex:

CommandButton1_Click()
Dim nIndex As Long
nIndex = ComboBox1.ListIndex
With ThisWorkbook.Sheets("MyDataSheet")
.Range("P2").Offset(nIndex, 0).Value = TextBox1.Value
.Range("Q2").Offset(nIndex, 0).Value = TextBox2.Value
.Range("R2").Offset(nIndex, 0).Value = TextBox3.Value
End With
'Other Stuff - e.g. Unload, Hide
End Sub
 
T

Todd

Here is how I have it all linked.

With Worksheets(4)
Set rng = .Range("M2:M100")
For Each cell In rng
If cell.Text = ComboBox1.Value Then
TextBox1.Value = .Cells(cell.Row, 16).Value
TextBox2.Value = .Cells(cell.Row, 17).Value
TextBox3.Value = .Cells(cell.Row, 18).Value
Exit For
End If
Next
End With
 
A

Anders S

Todd,

Just reverse it?

..Cells(cell.Row, 16).Value = TextBox1.Value

Regards,
Anders Silven
 
T

Todd Huttenstine

Thank you.

Thats exactly what I needed.

Todd
-----Original Message-----
How are the textboxes populated to begin with?

Assuming that they're based on something like the Combobox ListIndex:

CommandButton1_Click()
Dim nIndex As Long
nIndex = ComboBox1.ListIndex
With ThisWorkbook.Sheets("MyDataSheet")
.Range("P2").Offset(nIndex, 0).Value = TextBox1.Value
.Range("Q2").Offset(nIndex, 0).Value = TextBox2.Value
.Range("R2").Offset(nIndex, 0).Value = TextBox3.Value
End With
'Other Stuff - e.g. Unload, Hide
End Sub


.
Click to expand...
 
Top