VBA excel Update from user form command button


Hey Guys and Girls

I am a total newbie to vba, and would like your assistance. I have a workbook, with user forms. Currently, Switchboard, Userform1 and Userform2.

Userform1 is used to add a complaint, and works fine

Userform2 is used to edit the complaint. This is my problem.

I have the row listed in combobox, it then populates textboxes automatically.

I have 2 command buttons, one for "Update" and the other for Cancel.

"Update" when clicked, is to change the data, and then clear the form.

Any help would be appreciated.



Selected Answer

You are headed for a serious management problem. I urge you to invest time to give useful names to your forms, controls and variables so that you can concentrate on programming. For example 'CmdCancel' will tell you more than 'Commandbutton1'.

With that said, you need to understand the sequence of actions and then take control of them. You can't have control if you want VBA to do everything automatic, and you can't let VBA do everything automatic in a project as complex as the one you are building. Please understand the sequence.

  1. You create a Form (as an object)
  2. Now the form exists. You can access it, but you can't see it.
  3. The Initialise event occurs. You still can't see it, but it exists.
  4. You can write to the form or read from it.
  5. The Show command makes it visible. Now the controls on the form control everything. The code that called the form is waiting.
  6. The Hide command makes it invisible.You can still access it. (read/write)
  7. Unload deletes the form from memory.
  8. Set Form variable = Nothing clears the memory of the variable which used to hold the form which is already unloaded.

So, first of all you need to have a variable to hold your form. Create this in your click event, for example (please excuse the horrible naming - not my doing :-))

Private Sub CommandButton2_Click()
    Dim Form1 As UserForm1
    Set Form1 = New UserForm1           ' initialise event occurs
    With Form1
        .TextBox1.Text = Sheet4.Cells(5, "A").Value
        ' the remainder of the code runs when you Hide the form:
        Sheet4.Cells(5, "B").Value = .TextBox2.Text
    End With
    Unload Form1
    Set Form1 = Nothing
End Sub

Observe that the above code writes a value to TextBox1 after initialisation, meaning the Change event for TextBox1 will occur (you can more easily turn off events during initialisation). Instead of writing at this point you might do the same thing in the Initialise procedure or some event provedure or skip the procedures and do everything at this point.

The code gives control to the form on 'Show" and resumes when the form is hidden. The line of code reading from TextBox2 and writing to the sheet will run after the 'Hide' command. Your code uses 'Unload Me' instead of 'Hide' thereby killing this option. You meed to change that. Use 'Me.Hide' instead. But that line of code will run regardless of whether you pressed OK or Cancel. I usually set a flag in one of the Tag properties to indicate which button was pressed.

I took the liberty to modernise your initialse event. I hope this will give you some ideas. If this post does help you, please be sure to select it as a useful answer.

Private Sub UserForm_Initialize()
    ' all the form's event procedures are Private
    Const StartDay As Integer = -4      ' first day is Today - StartDay
    Dim CbxList() As String
    Dim i As Integer
    ReDim CbxList(14)                   ' reflecting -4 + 10 (+0)
    For i = 0 To UBound(CbxList)
        CbxList(i) = Format(Date + i + StartDay, "dd/mm/yyyy")
    Next i
    With ComboBox7
        .List = CbxList
        .ListIndex = 4                  ' today's date (?)
    End With
    With ComboBox2
        .List = Split("Walk in,Email,Telephone,Patrol", ",")
        .MatchEntry = fmMatchEntryNone
        .Value = "Select"
        .MatchEntry = fmMatchEntryFirstLetter
    End With
End Sub


Thank You Variatus,
You are pointing me in the right direction. I will be giving the naming some attention first, and then your coding.

Your answer is direct, and informative in  teaching a man to fish sort of way
Andre G (rep: 2) Sep 4, '17 at 6:00 am
Happy fishing then. Let me know when you need more bait.
Variatus (rep: 4148) Sep 4, '17 at 6:08 am
Add to Discussion


I'm new to the forum. I've been messing with userforms a little lately.
Which part is the problem?

Clearing the form can be achieved by using 'Unload Me' to close the form followed by 'Userform2.show' to reload the form.

You should be able to copy the code from Userform1 to update a row.
It's probably getting the next row in the sheet somehow, you can replace that with the row number you have used to populate the boxes on Userform2.

Hopefully that makes some sense.
I think this is my first post in any forum ever.



Thanks Marc, problems arises when one uses tables with dynamic ranges, find last row, and the insert row, all of them into tables-
Andre G (rep: 2) Sep 4, '17 at 5:58 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login