Excel VBA窗体作为输入界面实现数据快速录入

一、任务描述
通过窗体实现数据的较便捷录入，如下：

1497947236129060.jpg

二、 窗体相关基础知识1 插入窗体：VBE→菜单：插入→用户窗体；2 载入窗体：Load Userform13 显示窗体：Userform1.Show False后面的False参数表示在操作窗体的同时可以同时操作工作表；4 关闭窗体：Unload UserForm15 当前窗体：Me6 编辑和查看窗体代码：双击窗体；7 窗体事件7.1 窗体初始化事件7.2 控件事件7.2.1列表框单击事件Private Sub ListBox_Click()Dim MST As StringMST = ListBox1.ValueTextBox1.Value = MSTEnd Sub7.2.2 微调按钮单击事件
Private Sub SpinButton1_SpinUp()TextBox1.Value = TextBox1.Value - 1End Sub8 列表框控制可以与单元格相关联8.1 通过属性列表中设置；8.2 通过代码设置：ListBox1.RowSource = "Sheet!J2:J22"9 控件属性设置9.1 控件文本提示、标题、坐标、宽、高等；9.2 通过控件属性列表设置；9.3 通过代码批量设置Dim ctrlIndex As IntegerFor ctrlIndex = 1 To 5With Me.Controls("TextBox" & ctrlIndex).Left = 80.Top = 22 * (ctrlIndex - 1) + 12.Height = 18.Width = 100End With10 Tab顺序：窗体右击→Tab键顺序11 如何取得控件值11.1 文本框值：TextBox1.Value（或Text，也可省略）11.2 多选框值If OptionBox1.Value = True ThenCells(2,2).Value = OptionButton3.Caption11.3 复选框值If CheckBox1.Value = True ThenCells(2,2).Value = "体育"12 设置按钮自动响应Enter和Esc键按下CommandButton1.Default = TrueCommandButton2.Cancel = True三、 窗体代码Private Sub CommandButton1_Click()Dim lrow As Longlrow = Range("A" & Cells.Rows.Count).End(xlUp).Row + 1Range("A" & lrow) = month.ValueRange("B" & lrow) = rent.ValueRange("C" & lrow) = netfee.ValueRange("D" & lrow) = Range("E" & lrow - 1)Range("E" & lrow) = thisele.ValueRange("F" & lrow).Formula = "=(E" & lrow & "-D" & lrow & ")*1.3"Range("G" & lrow) = Range("H" & lrow - 1)Range("H" & lrow) = thiswater.ValueRange("I" & lrow).Formula = "=(H" & lrow & "-G" & lrow & ")*4.5"Range("J" & lrow).Formula = "=B" & lrow & "+C" & lrow & "+F" & lrow & "+I" & lrowRange("K" & lrow) = pay.ValueRange("L" & lrow).Formula = "=K" & lrow & "-J" & lrowRange("M" & lrow) = remark.ValueEnd SubPrivate Sub CommandButton2_Click()Unload MeEnd SubPrivate Sub CommandButton4_Click()Call UserForm_InitializeEnd SubPrivate Sub SpinButton1_SpinDown()month.Value = month.Value - 1End SubPrivate Sub SpinButton1_SpinUp()month.Value = month.Value + 1End SubPrivate Sub CommandButton3_Click()Dim lrow As Longlrow = Range("A" & Cells.Rows.Count).End(xlUp).Row + 1Rows(lrow - 1).SelectSelection.Delete Shift:=xlUpRange("A" & lrow - 2).SelectEnd SubPrivate Sub UserForm_Initialize()CommandButton1.Default = TrueCommandButton2.Cancel = TrueDim lrow As Longlrow = Range("A" & Cells.Rows.Count).End(xlUp).Row + 1month.Value = Range("A" & lrow - 1) + 1rent.Value = "1220"netfee.Value = "140"lastele.Value = Range("E" & lrow - 1)lastwater.Value = Range("H" & lrow - 1)Rows("2:2").SelectSelection.CopyActiveWindow.SmallScroll Down:=9Rows(lrow).SelectSelection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _SkipBlanks:=False, Transpose:=FalseApplication.CutCopyMode = FalseRange("A" & lrow).SelectEnd SubPrivate Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton1.BackColor = &H80000016End SubPrivate Sub CommandButton2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)CommandButton2.BackColor = &H80000016End SubPrivate Sub CommandButton3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)CommandButton3.BackColor = &H80000016End SubPrivate Sub CommandButton4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)CommandButton4.BackColor = &H80000016End SubPrivate Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)CommandButton1.BackColor = &H8000000FCommandButton2.BackColor = &H8000000FCommandButton3.BackColor = &H8000000FCommandButton4.BackColor = &H8000000FEnd Sub四、 工作簿打开事件代码Private Sub Workbook_Open()Userform1.Show FalseEnd Sub五、 模块代码Sub 打开窗体()Userform1.Show FalseEnd Sub