Forum Discussion

Copper Contributor

Apr 19, 2020

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

Zack Barresse

Iron Contributor

Apr 19, 2020

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

Zack Barresse
Iron Contributor
Apr 19, 2020
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.

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 21, 2020
You're my hero! Thank you so much for this! This is working just as I wanted it to work!!

Forum Discussion

VBA Update Selected Table Row

Share

Resources