Excel – Populate userform with records from current row

exceluserformvba

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 ?

Best Answer

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
Related Topic