excel - 用于自动化工作表的 VBA 代码

Question

1)我已经在 VBA 中创建了一个表单，并且我已经连接到第一张工作表，当我单击按钮时，表单会弹出以输入数据，但我想要的是，我想在第一张工作表中分配一个按钮，然后当我点击按钮时，表单应该出现，当我将数据插入表单时，它应该出现在第二张表中。

2)我将数据插入工作表中只有四行，完成后如果想修改特定列或特定行的数据，我们该怎么做，我需要有人建议我，我也请你发给我代码如何修改。

3)如果我想输入新数据，我还要求您将代码发送给我以清除工作表。

如果有人在这 3 点上帮助我，我会很高兴。

Private Sub cmdAdd_Click()
Dim i As Integer

'position cursor in the correct cell B9.
Range("B9:P40").Select
i = 1 'set as the first ID

'validate first four controls have been entered...
If Me.txtFName.Text = Empty Then 'Firstname
    MsgBox "Please enter firstname.", vbExclamation
    Me.txtFName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

If Me.txtSName.Text = Empty Then 'Surname
    MsgBox "Please enter surname.", vbExclamation
    Me.txtSName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

If Me.txtFuName.Text = Empty Then 'FullName
    MsgBox "Please enter fullname.", vbExclamation
    Me.txtFuName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

If Me.txtDName.Text = Empty Then 'Designation
    MsgBox "Please enter Designation.", vbExclamation
    Me.txtDName.SetFocus 'position cursor to try again
    Exit Sub 'terminate here - why continue?
End If

'if all the above are false (OK) then carry on.
'check to see the next available blank row start at cell B9...
Do Until ActiveCell.Value = Empty
    ActiveCell.Offset(1, 0).Select 'move down 1 row
    i = i + 1 'keep a count of the ID for later use
Loop

'Populate the new data values into the 'Data' worksheet.
ActiveCell.Value = i 'Next ID number
ActiveCell.Offset(0, 1).Value = Me.txtFName.Text 'set col B
ActiveCell.Offset(0, 2).Value = Me.txtSName.Text 'set col C
ActiveCell.Offset(0, 3).Value = Me.txtFuName.Text 'set col D
ActiveCell.Offset(0, 4).Value = Me.txtDName.Text 'set col E

'Clear down the values ready for the next record entry...
Me.txtFName.Text = Empty
Me.txtSName.Text = Empty
Me.txtFuName.Text = Empty
Me.txtDName.Text = Empty

Me.txtFName.SetFocus 'positions the cursor for next record entry

End Sub

Private Sub cmdClose_Click()
    'Close the form (itself)
    Unload Me
End Sub

Answer 1

一)在第一张表中放置一个按钮。

如果您使用的是表单控件，则将此代码放在模块中，然后右键单击按钮并单击分配宏，然后将其链接到以下代码

Sub Launch()
    UserForm1.Show
End Sub

如果您使用的是 ActiveX 控件，请在设计模式下双击它并使用此代码

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

关于在 Sheet2 中显示数据，在表单的“确定”按钮中，将其与第二张表链接。例如。

Private Sub CommandButton1_Click()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws           
        '~~> Assuming you want to write to only 1st 4 rows as you mentioned
        .Range("A1").Value = TextBox1.Text
        .Range("A2").Value = TextBox2.Text
        .Range("A3").Value = TextBox3.Text
        .Range("A4").Value = TextBox4.Text
    End With
End Sub

B) 为此，我建议接受用户的输入，然后根据该输入显示相关表格。



如果用户选择第一个选项，则显示您的数据输入用户表单，否则显示编辑用户表单，它看起来像数据输入用户表单，但会有 UserForm_Initialize()这将根据用户是要编辑行还是列来从工作表中提取数据。您如何选择接受该输入是我留给您的。例如

Private Sub UserForm_Initialize()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws
        TextBox1.Text = .Range("A1").Value
        TextBox2.Text = .Range("A2").Value
        TextBox3.Text = .Range("A3").Value
        TextBox4.Text = .Range("A4").Value
    End With
End Sub

C) 要清除工作表，您可以使用此代码。

Private Sub CommandButton2_Click()
    ThisWorkbook.Sheets("Sheet2").Cells.ClearContents
End Sub