Populate userform with records from current row

Question

I have a userform with several textboxes, radio buttons, dropdowns, etc. When a user creates a new entry, the data is saved on the Data sheet, with one record occupying one row. Now I want to be able to click an "edit" button in column A which allows to load the userform preloaded with the data from this row.

The issue is that when the form is loaded, the initialization macro resets all form fields to "", and I havent figured out how to tell VBA to load the calling row's data.

Any suggestions on how to go about this?

---

Here the code I have so far: call the userform when the NEW ENTRY button is clicked

Sub call_userform()

Details.Show

End Sub

when the userform is initialized:

Private Sub UserForm_Initialize()

IC_logo.BackColor = RGB(81, 81, 73) ' ash grey

'Empty all fields
status.Value = "Open"
serial = Evaluate("randbetween(10000,30000)")
priority.Value = ""
created_on.Value = Format(Date, "dd/mm/yyyy")
created_by.Value = ""
department.Value = ""
floor.Value = ""
area.Value = ""
subarea.Value = ""
details.Value = ""
fu_name.Value = ""
fu_department = ""

Me.status.RowSource = "lst_status"              'Fill Status
Me.priority.RowSource = "lst_priority"          'Fill Priorities
created_by = Sheets("Settings").Range("B24")    'Fill Created By with Logon Username
department = Sheets("Settings").Range("B25")    'Fill Created By with Logon Department
Me.floor.RowSource = "lst_floor"                'Fill Floor
Me.area.RowSource = "lst_area"                  'Fill Area
Me.subarea.RowSource = "lst_subarea"            'Fill Subarea

'Set follow up to construction company as per default
'fu_2.Value = True

'Set Focus on NameTextBox
priority.SetFocus

End Sub

when the SAVE button is clicked

Private Sub btn_save_Click()
Dim emptyRow As Long

'Activate Data sheet
Sheets("Data").Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1

'Transfer information
Cells(emptyRow, 2).Value = serial.Value
Cells(emptyRow, 4).Value = created_on.Value
Cells(emptyRow, 5).Value = created_by.Value
Cells(emptyRow, 6).Value = priority.Value
Cells(emptyRow, 7).Value = floor.Value
Cells(emptyRow, 8).Value = area.Value
Cells(emptyRow, 9).Value = subarea.Value
Cells(emptyRow, 10).Value = details.Value

If fu_1.Value = True Then
    Cells(emptyRow, 11).Value = fu_1.Caption
End If
If fu_2.Value = True Then
    Cells(emptyRow, 11).Value = fu_2.Caption
End If
If fu_3.Value = True Then
    Cells(emptyRow, 11).Value = fu_3.Caption
End If
If fu_4.Value = True Then
    Cells(emptyRow, 11).Value = fu_4.Caption
End If

If fu_name.Value > 0 Or fu_department.Value > 0 Then
    Cells(emptyRow, 12).Value = fu_name.Value & " " & fu_department.Value
End If

Cells(emptyRow, 13).Value = status.Value

End Sub

As mentioned, the problem now is how do I load the userform with the data of the current row? Would that still be via details.show ?

Answer 1

I'd go like follows:

• use Tag property of the UserForm object to store a "calling parameter" that will tell UserForm whether to run an InitializeValues() Sub or a FillValues() one

• use UserForm_Activate event handler to have UserForm decide which action is to be taken

so, assuming you attach an Edit() sub to your sheet "edit" buttons, the former would be

Sub Edit()
    With UserForm3
        .Tag = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row '<~~ tell the UserForm there's something to bring in so that it'll fill controls from the sheet instead of initializing them
        .Show
        .Tag = "" '<~~ bring Tag property back to its "null" value
    End With
    Unload UserForm3
End Sub

then in you UserForm code pane place this

Private Sub UserForm_Activate()
    If Me.Tag = "" Then '<~~ if there's no info from Tag property...
        InitializeValues '<~~ ... then Initialize controls values
    Else
        FillValues '<~~ ...otherwise fill controls with sheet values
    End If
End Sub

Private Sub InitializeValues()
    With Me
        .ComboBox1.RowSource = "initRange"
        .serial.Value = "actText1"
        .created_on.Value = "actText2"
        .created_by.Value = "actText3"
        ' and so on...
    End With
End Sub

Private Sub FillValues()
    With Me
        .serial.Value = Cells(.Tag, 2).Value
        .created_on.Value = Cells(.Tag, 4).Value
        .created_by.Value = Cells(.Tag, 5).Value
        '.. and so on
    End With
End Sub

Answer 2

If you have one Button on your sheet to call the form, then just use something like this:

me.TextBox1.value = Cells(ActiveCell.Row, 2).Value 'ActiveCell.Row returns the row number of the current cell

So the only command the button has is UserForm1.Show

Answer 3

You can write a sub like loadDataIntoForm(rowNum As Long) that fills the fields of the form to your liking and put it into the code of the userform. e.g.

Public Sub loadDataIntoForm(rowNum As Long)
    me.somecontrol.Value = Sheets("Data").cells(rowNum,1).Value
    '...
End Sub

Then if the edit button is clicked execute

Details.loadDataIntoForm(rowNum) 'where rowNum is something like ActiveCell.Row. This calls UserForm_Initialize and THEN loadDataIntoForm
Details.Show 'UserForm_Initialize won't get executed again

UserForm_Initialize is executed before loadDataIntoForm is so the fields get cleared first by the UserForm_Initialize function and then re-populated by loadDataIntoForm. To be sure you could also load the form first

Load Details
Details.loadDataIntoForm(ActiveCell.Row)
Details.Show

So you can be sure UserForm_Initialize is not called after loadDataIntoForm.

To get a sense of what is called when you can go through the code line by line using F8.

---

edit: A nicer way would probably be to write two subs, one for initializing the form for an edit (populate the fields) and one for initializing the form for a new record (clear the fields)

It could look something like

Public Sub ShowNew()
    'clear all the controls
    me.Show
End Sub

Public Sub ShowEdit(rowNum As Long)
    'populate the controls
    me.Show
End Sub

Then instead of using Details.Show use Details.ShowNew and Details.ShowNew(rowNum)