VBA Update Selected Table Row

[Patrick Thörnlund]

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 table and edit that row. 

I've attatched the file which I've been working on where I have created a form called edit_frm where I would like the current row information to be edited. If anyone could help me out with this I would greatly appreciate it!

Thank you!

[#2]

@Patrick Thörnlund I would think about utilizing a property for the range of the Table you're wanting to pass to the userform. Then you can manipulate the object when/however you want.

The routine 'edit_button_Klicka' would be transformed into:

Option Explicit


Private pEditTable As ListObject
Private pEditRow As ListRow


Public Property Get EditTable() As ListObject
    Set EditTable = pEditTable
End Property


Public Property Set EditTable( _
       ByVal Value As ListObject _
       )

    Set pEditTable = Value

End Property


Public Property Get EditRow() As ListRow
    Set EditRow = pEditRow
End Property


Public Property Set EditRow( _
       ByVal Value As ListRow _
       )

    Set pEditRow = Value

End Property


Sub edit_button_Klicka()

    Set EditTable = Worksheets("Beställningsunderlag").ListObjects("Tabell1")

    On Error Resume Next
    If Intersect(ActiveCell, EditTable.DataBodyRange) Is Nothing Then
        MsgBox "Please select a row in the Table."
        Exit Sub
    End If

    Set EditRow = EditTable.ListRows(ActiveCell.Row - EditTable.DataBodyRange.Cells(1, 1).Row + 1)
    On Error GoTo 0

    If EditRow Is Nothing Then
        MsgBox "Could not find the selected row.", vbExclamation + vbOKOnly
        Exit Sub
    End If

    edit_frm.Show

End Sub

In your userform, you would need an initialize event to see if that property has been set or not, which you could do with something like this...

Private Sub UserForm_Initialize()

    If EditRow Is Nothing Then Exit Sub
    Me.cbox_bolag.Value = EditTable.DataBodyRange(EditRow.Index, EditTable.ListColumns(2).Index).Value
    ' Add other lines for your userform controls...
    
End Sub

Since the properties are public, I would destroy them when closing the userform as well, with something like this...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
    Set EditRow = Nothing
    Set EditTable = Nothing
    
End Sub

As another method, opposed to the public property example I showed above, you could have a wrapper function for calling your userform and just handle it all in the initialize event. I like the public property method because it's more flexible, as you can re-use the code in other situations.

HTH

[#3]

Oh, I also forgot to mention that, when exiting the userform (via confirm button), you would also need to go through the same process of utilizing the 'EditRow' to place the values back. I would test them first (i.e. If Control.Value <> Cell.Value Then...) as to avoid triggering a cell change to the same value, but that's mere preference tbh.

[#4]

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.

[#5 · Accepted solution]

@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.

[#6]

You're my hero! Thank you so much for this! This is working just as I wanted it to work!!

[#7]

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!

[#8]

@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). 

[#9]

Huge thanks @Zack Barresse, I'm so grateful for your help! Everything works as intended now, this really made my day!

[#10]

Very glad you got it working how you wanted. :)