Forum Discussion

Copper Contributor

Apr 19, 2020

Solved

VBA Update Selected Table Row

Hi! I'm trying to create my first form in VBA. I've created an input form where a user can input a new row in a table, but I would also like users to be able to select an existing row in the tab...

Order Form Help.xlsm43 KB

Macros and VBA

Zack Barresse

Apr 20, 2020

Patrick Thörnlund You're very close! Userforms have events. One of those being when the form is opened/loaded and it's called 'UserForm_Initialize'. This code runs every time the form Load function is called, which is done by default if you Show it.

Here are two examples which do exactly the same thing:

Load edit_frm
edit_frm.Show

... does the same thing as ...

edit_frm.Show

When you call the Show method, the form loads, which runs the Initialize event. In this example it doesn't make much of a difference, but if you try using userforms a bit more, it's good to know. For example, loading a userform as a variable and controlling it via code.

The Initialize event in the workbook you attached is very close. I see where you added the code I posted. Where it's failing is there are three rows of your old code which shouldn't be there. Remove those rows, starting with "Dim r As Long".

Next, copy the line of code I wrote for the Initialize event for each control you want to load. In my example I used the index of the Table column. You can also use the text name as well, which might be easier to understand. Here are two examples...

Me.cbox_bolag.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Företag").Index).Value
Me.tbox_bestdatum.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Beställningsdatum").Index).Value

In looking through your Table, there are some tricky items, such as check boxes. Because of those, I wrote up your entire Initialize routine, below, to save confusion.

Private Sub UserForm_Initialize()

    If EditRow Is Nothing Then Exit Sub
    
    Me.cbox_bolag.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Företag").Index).Value
    Me.tbox_bestdatum.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Beställningsdatum").Index).Value
    Me.tbox_division.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Division").Index).Value
    Me.tbox_namnkpers.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kontrollpersonens namn").Index).Value
    Me.tbox_persnmr.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Personnummer").Index).Value
    Me.cbox_niva.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Nivå").Index).Value
    Me.tbox_levdatum.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Leveransdatum").Index).Value
    Me.cbox_handlaggare.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Ansvarig handläggare").Index).Value
    Me.cbox_utfall.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utfall").Index).Value
    Me.cbox_bas.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS").Index).Value
    Me.cbox_media.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media").Index).Value
    Me.cbox_arbetsliv.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv").Index).Value
    Me.cbox_utbildning.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning").Index).Value
    Me.cbox_domstol.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstolsutskick").Index).Value
    Me.cbox_aklagare.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagarmyndighet").Index).Value
    Me.tbox_ovrigt.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Övriga uppgifter").Index).Value

    
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kvitterad order").Index).Value = "Ja" Then
        Me.Kvitterad_order_ja.Value = True
        Me.Kvitterad_order_nej.Value = False
    Else
        Me.Kvitterad_order_nej.Value = True
        Me.Kvitterad_order_ja.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Fakturerad").Index).Value = "Ja" Then
        Me.CheckBox_fakturerad.Value = True
    Else
        Me.CheckBox_fakturerad.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS Klar").Index).Value = "Ja" Then
        Me.CheckBox_BAS.Value = True
    Else
        Me.CheckBox_BAS.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media Klar").Index).Value = "Ja" Then
        Me.CheckBox_media.Value = True
    Else
        Me.CheckBox_media.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv Klar").Index).Value = "Ja" Then
        Me.CheckBox_arbetsliv.Value = True
    Else
        Me.CheckBox_arbetsliv.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning Klar").Index).Value = "Ja" Then
        Me.CheckBox_BAS.Value = True
    Else
        Me.CheckBox_BAS.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstol Klar").Index).Value = "Ja" Then
        Me.CheckBox_domstol.Value = True
    Else
        Me.CheckBox_domstol.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagare Klar").Index).Value = "Ja" Then
        Me.CheckBox_aklagare.Value = True
    Else
        Me.CheckBox_aklagare.Value = False
    End If
    
End Sub

Remember, for this to work you must have a row in the Table selected, then click the 'Redigera Order' button to load the form with that Table's row of data. I've re-attached your file with the code in it.

Order Form Help_Zack Code v1.02.xlsm46 KB

Patrick Thörnlund

Copper Contributor

Apr 20, 2020

Thank you Zack Barresse!

However I don't fully understand what you mean. I tried to use the code you provided but don't get it to work, when I open the edit form nothing happens.

As I said before I've never worked with VBA so I'm sorry if I'm missing somthing obvious, thank you for your patience! I've attached the file where I tried to use the code you provided.

Order Form Help_Zack Code.xlsm44 KB

Zack Barresse

Iron Contributor

Apr 20, 2020

Here are two examples which do exactly the same thing:

Load edit_frm
edit_frm.Show

... does the same thing as ...

edit_frm.Show

Me.cbox_bolag.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Företag").Index).Value
Me.tbox_bestdatum.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Beställningsdatum").Index).Value

In looking through your Table, there are some tricky items, such as check boxes. Because of those, I wrote up your entire Initialize routine, below, to save confusion.

Private Sub UserForm_Initialize()

    If EditRow Is Nothing Then Exit Sub
    
    Me.cbox_bolag.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Företag").Index).Value
    Me.tbox_bestdatum.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Beställningsdatum").Index).Value
    Me.tbox_division.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Division").Index).Value
    Me.tbox_namnkpers.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kontrollpersonens namn").Index).Value
    Me.tbox_persnmr.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Personnummer").Index).Value
    Me.cbox_niva.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Nivå").Index).Value
    Me.tbox_levdatum.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Leveransdatum").Index).Value
    Me.cbox_handlaggare.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Ansvarig handläggare").Index).Value
    Me.cbox_utfall.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utfall").Index).Value
    Me.cbox_bas.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS").Index).Value
    Me.cbox_media.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media").Index).Value
    Me.cbox_arbetsliv.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv").Index).Value
    Me.cbox_utbildning.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning").Index).Value
    Me.cbox_domstol.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstolsutskick").Index).Value
    Me.cbox_aklagare.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagarmyndighet").Index).Value
    Me.tbox_ovrigt.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Övriga uppgifter").Index).Value

    
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kvitterad order").Index).Value = "Ja" Then
        Me.Kvitterad_order_ja.Value = True
        Me.Kvitterad_order_nej.Value = False
    Else
        Me.Kvitterad_order_nej.Value = True
        Me.Kvitterad_order_ja.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Fakturerad").Index).Value = "Ja" Then
        Me.CheckBox_fakturerad.Value = True
    Else
        Me.CheckBox_fakturerad.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS Klar").Index).Value = "Ja" Then
        Me.CheckBox_BAS.Value = True
    Else
        Me.CheckBox_BAS.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media Klar").Index).Value = "Ja" Then
        Me.CheckBox_media.Value = True
    Else
        Me.CheckBox_media.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv Klar").Index).Value = "Ja" Then
        Me.CheckBox_arbetsliv.Value = True
    Else
        Me.CheckBox_arbetsliv.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning Klar").Index).Value = "Ja" Then
        Me.CheckBox_BAS.Value = True
    Else
        Me.CheckBox_BAS.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstol Klar").Index).Value = "Ja" Then
        Me.CheckBox_domstol.Value = True
    Else
        Me.CheckBox_domstol.Value = False
    End If
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagare Klar").Index).Value = "Ja" Then
        Me.CheckBox_aklagare.Value = True
    Else
        Me.CheckBox_aklagare.Value = False
    End If
    
End Sub

Order Form Help_Zack Code v1.02.xlsm46 KB

Patrick Thörnlund

Copper Contributor

Apr 27, 2020

Hi again Zack!

Thank you again for helping me out here. There was one thing I noticed in the form though. When I save the edit form a new row is added instead of the old row being updated. Is there any way to rewrite the save function so that the same row you selected is updated? I understand this was not the original question, but if you would be able to help me out with this as well you would have my eternal gratitude!!

Thank you!

Zack Barresse

Iron Contributor

Apr 27, 2020

Patrick Thörnlund yes, of course. I probably should've mentioned that. In order to do this you would basically reverse what we did in the Initialize event. So your 'submit' button click routine would end up looking like this:

Private Sub button_submit_Click()

    If EditRow Is Nothing Then Exit Sub
    
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Företag").Index).Value = Me.cbox_bolag.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Beställningsdatum").Index).Value = Me.tbox_bestdatum.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Division").Index).Value = Me.tbox_division.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kontrollpersonens namn").Index).Value = Me.tbox_namnkpers.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Personnummer").Index).Value = Me.tbox_persnmr.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Nivå").Index).Value = Me.cbox_niva.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Leveransdatum").Index).Value = Me.tbox_levdatum.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Ansvarig handläggare").Index).Value = Me.cbox_handlaggare.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utfall").Index).Value = Me.cbox_utfall.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS").Index).Value = Me.cbox_bas.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media").Index).Value = Me.cbox_media.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv").Index).Value = Me.cbox_arbetsliv.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning").Index).Value = Me.cbox_utbildning.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstolsutskick").Index).Value = Me.cbox_domstol.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagarmyndighet").Index).Value = Me.cbox_aklagare.Value
    EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Övriga uppgifter").Index).Value = Me.tbox_ovrigt.Value

    If Me.Kvitterad_order_ja.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kvitterad order").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kvitterad order").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_fakturerad.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Fakturerad").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Fakturerad").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_BAS.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS Klar").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("BAS Klar").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_media.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media Klar").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Media Klar").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_arbetsliv.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv Klar").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Arbetsliv Klar").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_BAS.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning Klar").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Utbildning Klar").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_domstol.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstol Klar").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Domstol Klar").Index).Value = "Nej"
    End If
    
    If Me.CheckBox_aklagare.Value Then
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagare Klar").Index).Value = "Ja"
    Else
        EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Åklagare Klar").Index).Value = "Nej"
    End If
    
    Unload Me
    
End Sub

I would also change some of the functionality of the Ja/Nej combo buttons, so that when you click one it reverses the other. For example the 'Kvitterad order?' comboboxes should toggle each other. You would add code like below to do that:

Private Sub Kvitterad_order_ja_Click()
     Me.Kvitterad_order_nej.Value = Not Me.Kvitterad_order_ja.Value
End Sub

Private Sub Kvitterad_order_nej_Click()
    Me.Kvitterad_order_ja.Value = Not Me.Kvitterad_order_nej.Value
End Sub

In doing this, you can shorten your Initialize code a little bit. Here is one example using the 'Kvitterad_order_ja' and 'Kvitterad_order_nej' controls:

    ' This part can be removed...
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kvitterad order").Index).Value = "Ja" Then
        Me.Kvitterad_order_ja.Value = True
        Me.Kvitterad_order_nej.Value = False
    Else
        Me.Kvitterad_order_nej.Value = True
        Me.Kvitterad_order_ja.Value = False
    End If
    
    ' And be replaced by this...
    If EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns("Kvitterad order").Index).Value = "Ja" Then
        Me.Kvitterad_order_ja.Value = True
    Else
        Me.Kvitterad_order_nej.Value = True
    End If

I would also recommend setting your 'submit' button ('Spara Order') Default property to True. You also might want to think about changing 'Rensa Formulär' Cancel property to True. These are two properties which should almost always be set to every userform.

Default property

When a form loads, this action defaults to when the user presses the ENTER key, which will fire that button's click event.

Cancel property

When a form loads, this action defaults to when the user presses the ESC key, which will fire that button's click event.

To set these properties, just select the button and look for those properties in the Properties pane, change the values to True/False manually (there is no dropdown).

Patrick Thörnlund
Copper Contributor
Apr 28, 2020
Huge thanks Zack Barresse, I'm so grateful for your help! Everything works as intended now, this really made my day!

Patrick Thörnlund
Copper Contributor
Apr 21, 2020
You're my hero! Thank you so much for this! This is working just as I wanted it to work!!