Update ActiveRow from textbox excel vba

Question

Some help please - trying to keep it simple

I have a UserForm which updates text boxes from selection in ComboBox. I would like to be able to change these populated boxes, and then with Update command, change the values in the sheet and then clear the form

I've been able to create the UserForm that adds the data, but i struggle to let VBA select the active cell and active row from the control box...????

Once I can get VBA to the correct AvtiveCell, i can use offset and change/add as needed

Private Sub ComboBox1_Change()
    With Me
        .TextBox1.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 2)
        .TextBox2.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 3)
        .TextBox3.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 4)
        .TextBox4.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 5)
        .TextBox5.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 6)
        .TextBox6.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 7)
        .TextBox7.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 8)
        .TextBox8.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 9)
        .TextBox9.Value = Sheet1.Cells(.ComboBox1.ListIndex + 2, 10)
    End With
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub EditAddButton_Click()
    EditAdd
End Sub

Private Sub UserForm_Initialize()
    TextBox1.SetFocus
End Sub

Can you paste your current userform code? We need to see how far you've already got. — Chris Melville

Chris Melville Chris Melville · Accepted Answer · 2017-09-10T17:46:22

Ok, so you have the autopopulation done. Now you need to add code to your EditAdd click sub. Presumably you want to replace the cell values on the sheet with the contents of those in the userform.

What have you tried so far? I would simply try reversing the assignments! For example, in that sub, do something like this:

Private Sub EditAddButton_Click()
    Dim ComplaintNum as Integer
    ComplaintNum = ComboBox1.ListIndex + 2
    With Sheet1
        .cells(ComplaintNum, 2) = TextBox1.Value
        ' etc down the list
    End With
End Sub

I have not tested this - but give it a go and play around with it. You'll also need to handle adding new rows, but I won't write that for you yet :)

Update ActiveRow from textbox excel vba

1 Answers