vba Excel 用户表单 - 如何从电子表格中获取数据以再次显示在用户表单中

Excel User Form - how to get data from spreadsheet to show in user form again

提问by user3817138

Ok, I am a novice at VBA. I created a user form in Excel that correctly loads the data into the spreadsheet and has command buttons that work. However, how can I get a line of data added to the spreadsheet to redisplay in the form. I know Excel has the form view that does this but the boxes are so small you can not see all of the data.

好的，我是 VBA 的新手。我在 Excel 中创建了一个用户表单，该表单将数据正确加载到电子表格中，并具有有效的命令按钮。但是，如何将一行数据添加到电子表格中以重新显示在表单中。我知道 Excel 具有执行此操作的表单视图，但是这些框太小了，您无法看到所有数据。

Private Sub AbstractReceipt_Initalize()

End Sub
Private Sub AbstractReceipt_Click()

    'ID = Sheet1.Range("A")
    'Abstract_Receipt_No = Sheet1.Range("B")
    'Date = Sheet1.Range("C")
    'Attorney_Name = Sheet.Range("D")
    'Client_Name = Sheet.Range("E")
    'Abstract_Co_Name = Sheet.Range("F")
    'Legal_Description = Sheet.Range("G")
    'Title_Opinion_No = Sheet.Range("H")
    'Signature_Information = Sheet.Range("I")
    'Other_Names = Sheet.Range ("J")


AbstractReceipt.IDBox.Text = ID
AbstractReceipt.AbReceiptBox.Text = Abstract_Receipt_No
AbstractReceipt.DateTextBox.Text = Date
AbstractReceipt.AttorneyTextBox.Text = Attorney_Name
AbstractReceipt.ClientTextBox.Text = Client_Name
AbstractReceipt.AbCoTextBox.Text = Abstract_Co_Name
AbstractReceipt.LegalTextBox.Text = Legal_Description
AbstractReceipt.TOTextBox.Text = Title_Opinion_No
AbstractReceipt.SigTextBox.Text = Signature_Information
AbstractReceipt.OtherTextBox.Text = Other_Names


End Sub

Private Sub AbCoTextBox_Enter()
AbCoTextBox.Text = ""

End Sub

Private Sub AbReceiptTextBox_Enter()
AbReceiptTextBox.Text = ""

End Sub

Private Sub AttorneyTextBox_Enter()
AttorneyTextBox.Text = ""
End Sub


Private Sub ClientTextBox_Enter()
ClientTextBox.Text = ""

End Sub

Private Sub CommandButton1_Click()
AbstractReceipt.PrintForm

End Sub

Private Sub CommandButton2_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub CommandButton3_Click()
    Dim lRow As Long
    Dim lAutoNo As Long

    With Sheet1
        lAutoNo = Application.WorksheetFunction.Max(Sheet1.Columns(1))
        lAutoNo = lAutoNo + 1

        bEditing = True

        lRow = .UsedRange.Rows.Count + 1
        .Cells(lRow, 1).Value = lAutoNo
        .Cells(lRow, 2).Value = Me.AbReceiptTextBox.Value
        .Cells(lRow, 3).Value = Me.AttorneyTextBox.Value
        .Cells(lRow, 4).Value = Me.ClientTextBox.Value
        .Cells(lRow, 5).Value = Me.AbCoTextBox.Value
        .Cells(lRow, 6).Value = Me.LegalTextBox.Value
        .Cells(lRow, 7).Value = Me.AbCoTextBox.Value
        .Cells(lRow, 8).Value = Me.TOTextBox.Value
        .Cells(lRow, 9).Value = Me.SigTextBox.Value
        .Cells(lRow, 10).Value = Me.OtherTextBox.Value
        bEditing = True
    End With
End Sub

Private Sub DateTextBox_Enter()
DateTextBox.Text = ""

End Sub


Private Sub Frame1_Click()

End Sub

Private Sub LegalTextBox_Enter()
LegalTextBox.Text = ""

End Sub


Private Sub OtherTextBox_Enter()
OtherTextBox.Text = ""

End Sub

Private Sub SigTextBox_Enter()
SigTextBox.Text = ""

End Sub

Private Sub TOTextBox_Enter()
TOTextBox.Text = ""

End Sub

Private Sub CancelCommandButton_Click()
    Unload Me

End Sub

采纳答案by StandardDeviation

Here is a quick example:

这是一个快速示例：

Prerequisites: Empty worksheet with a user form named "UserForm1" which has one textbox named "TextBox1" and one button named "Command Button1".

先决条件：具有名为“UserForm1”的用户表单的空工作表，其中有一个名为“TextBox1”的文本框和一个名为“Command Button1”的按钮。

Scenario: If a selection changes on the worksheet, UserForm1 shows with ActiveCell value in TextBox1. on CommandButton1 click, UserForm1 hides and unloads.

场景：如果工作表上的选择更改，UserForm1 将在 TextBox1 中显示 ActiveCell 值。在 CommandButton1 单击，UserForm1 隐藏和卸载。

Code can be reused to meet specific requirements:

代码可以重用以满足特定要求：

Worksheet code:

工作表代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      UserForm1.Show
      UserForm1.TextBox1.Value = ActiveCell.Value
End Sub

UserForm1 code:

用户窗体 1 代码：

Private Sub CommandButton1_Click()
      UserForm1.Hide
      Unload UserForm1
End Sub

回答by Wabonano

At the end of the "yourbutton_click" you can update the textbox like

在“yourbutton_click”结束时，您可以更新文本框，如

textbox.text = range("yourrange").value

Hope it helps, and you can also elaborate more your question.

希望它有所帮助，您也可以详细说明您的问题。