UserForms to Edit Text with VBA

Question

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.

Gove · Accepted Answer · 2022-04-12 14:24:30Z

1

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

edited Apr 12, 2022 at 14:24

answered Apr 12, 2022 at 12:34

Gove

1,82411 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rich Henderson Over a year ago

That is amazing, THANK YOU!!! How do I save the edits in the textbox back into the spreadsheet?

Gove Over a year ago

That's a different question. Go ahead and accept this answer, post another question and put a comment here when done. I'll take a look and answer there. I've adjusted this code in preparation.

Rich Henderson Over a year ago

Thank you, I have posted a new question. I am in awe!!!

Gove Over a year ago

Ok. I'll answer it there. I would not mind an upvote for this answer and the other if does what you need.

Collectives™ on Stack Overflow

UserForms to Edit Text with VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related