UserForms to Edit Text with VBA-CodePudding

I have a good process now set up to add data into Excel using a series of UserForms, but I want to be able to edit the data with UserForms rather than look for it in the spreadsheets.

I have two ComboBox's in UserForm1: ComboBox1 - relating to Column A in Sheet1 ComboBox2 - relating to Column B in Sheet1

When both of these match data in a row, then I want the rest of the row (Column's C to F) to populate TextBox1-4 and allow me to edit the data making real-time changes within the spreadsheet.

I have been going around in circles on this! Any help would be much apricated, as even setting this type of argument now exceeds my VBA knowledge.

CodePudding user response：

This code is the code for the form. Noice that the "Dim Row..." statement must be at the very top of the code module. This is to allow you to keep track of which row you found the data on so you can write it back to the worksheet later:

Dim row As Long

Private Sub CommandButton1_Click()
    Dim s As Worksheet
    Set s = ActiveSheet ' use for active sheet
    'Set s = Worksheets("Sheet1") ' use for a specific sheet
    For row = 1 To s.Cells(s.Rows.Count, 1).End(xlUp).row
      If s.Cells(row, "A").Value = ComboBox1.Value And _
          s.Cells(row, "B").Value = ComboBox2.Value Then
          TextBox1.Value = s.Cells(row, "C").Value
          TextBox2.Value = s.Cells(row, "D").Value
          TextBox3.Value = s.Cells(row, "E").Value
          TextBox4.Value = s.Cells(row, "F").Value
      End If
    Next
End Sub