用当前行的记录填充用户表单 [英] Populate userform with records from current row

问题描述

我有一个包含多个文本框、单选按钮、下拉菜单等的用户表单.当用户创建新条目时，数据将保存在数据表中，一条记录占据一行.现在我希望能够单击 A 列中的编辑"按钮，该按钮允许加载预加载了该行数据的用户表单.

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.

问题是当加载表单时，初始化宏将所有表单字段重置为"，我还没有弄清楚如何告诉 VBA 加载调用行的数据.

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?

这是我到目前为止的代码:单击 NEW ENTRY 按钮时调用用户表单

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

Sub call_userform()

Details.Show

End Sub

初始化用户表单时:

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

点击保存按钮时

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

如前所述，现在的问题是如何使用当前行的数据加载用户表单?那仍然是通过 details.show 吗?

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 ?

推荐答案

我会这样做:

• 使用 UserForm 对象的 Tag 属性来存储一个调用参数"，它会告诉用户窗体是否运行 InitializeValues()code> Sub 或 FillValues() 一个

• 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

使用 UserForm_Activate 事件处理程序让 UserForm 决定要采取的行动

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

因此，假设您将 Edit() 子附加到您的工作表编辑"按钮，前者将是

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

这篇关于用当前行的记录填充用户表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！