Forum Discussion

Patrick Thörnlund's avatar
Patrick Thörnlund
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 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!

Macros and VBA
  • Zack Barresse's avatar
    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.

  • Zack Barresse's avatar
    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's avatar
      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's avatar
      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.

      • Zack Barresse's avatar
        Zack Barresse
        Iron Contributor
        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.

Resources