Apr 19 2020 08:12 AM
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!
Apr 19 2020 01:36 PM
@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
Apr 19 2020 01:38 PM
Apr 19 2020 11:56 PM
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.
Apr 20 2020 10:34 AM - edited Apr 20 2020 10:35 AM
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.
Apr 21 2020 12:07 AM
Apr 27 2020 01:53 AM
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!
Apr 27 2020 09:59 AM
@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).
Apr 28 2020 01:50 AM
Huge thanks @Zack Barresse, I'm so grateful for your help! Everything works as intended now, this really made my day!
Apr 28 2020 09:00 AM
Apr 20 2020 10:34 AM - edited Apr 20 2020 10:35 AM
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.