一、任务描述
通过窗体实现数据的较便捷录入，如下：
二、 窗体相关基础知识
1 插入窗体：VBE→菜单：插入→用户窗体；
2 载入窗体：Load Userform1
3 显示窗体：Userform1.Show False
后面的False参数表示在操作窗体的同时可以同时操作工作表；
4 关闭窗体：Unload UserForm1
5 当前窗体：Me
6 编辑和查看窗体代码：双击窗体；
7 窗体事件
7.1 窗体初始化事件
7.2 控件事件
7.2.1列表框单击事件
Private Sub ListBox_Click()
Dim MST As String
MST = ListBox1.Value
TextBox1.Value = MST
End Sub
7.2.2 微调按钮单击事件
Private Sub SpinButton1_SpinUp()
TextBox1.Value = TextBox1.Value - 1
End Sub
8 列表框控制可以与单元格相关联
8.1 通过属性列表中设置；
8.2 通过代码设置：ListBox1.RowSource = "Sheet!J2:J22"
9 控件属性设置
9.1 控件文本提示、标题、坐标、宽、高等；
9.2 通过控件属性列表设置；
9.3 通过代码批量设置
Dim ctrlIndex As Integer
For ctrlIndex = 1 To 5
With Me.Controls("TextBox" & ctrlIndex)
.Left = 80
.Top = 22 * (ctrlIndex - 1) + 12
.Height = 18
.Width = 100
End With
10 Tab顺序：窗体右击→Tab键顺序
11 如何取得控件值
11.1 文本框值：TextBox1.Value（或Text，也可省略）
11.2 多选框值
If OptionBox1.Value = True Then
Cells(2,2).Value = OptionButton3.Caption
11.3 复选框值
If CheckBox1.Value = True Then
Cells(2,2).Value = "体育"
12 设置按钮自动响应Enter和Esc键按下
CommandButton1.Default = True
CommandButton2.Cancel = True
三、 窗体代码
Private Sub CommandButton1_Click()
Dim lrow As Long
lrow = Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
Range("A" & lrow) = month.Value
Range("B" & lrow) = rent.Value
Range("C" & lrow) = netfee.Value
Range("D" & lrow) = Range("E" & lrow - 1)
Range("E" & lrow) = thisele.Value
Range("F" & lrow).Formula = "=(E" & lrow & "-D" & lrow & ")*1.3"
Range("G" & lrow) = Range("H" & lrow - 1)
Range("H" & lrow) = thiswater.Value
Range("I" & lrow).Formula = "=(H" & lrow & "-G" & lrow & ")*4.5"
Range("J" & lrow).Formula = "=B" & lrow & "+C" & lrow & "+F" & lrow & "+I" & lrow
Range("K" & lrow) = pay.Value
Range("L" & lrow).Formula = "=K" & lrow & "-J" & lrow
Range("M" & lrow) = remark.Value
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton4_Click()
Call UserForm_Initialize
End Sub
Private Sub SpinButton1_SpinDown()
month.Value = month.Value - 1
End Sub
Private Sub SpinButton1_SpinUp()
month.Value = month.Value + 1
End Sub
Private Sub CommandButton3_Click()
Dim lrow As Long
lrow = Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
Rows(lrow - 1).Select
Selection.Delete Shift:=xlUp
Range("A" & lrow - 2).Select
End Sub
Private Sub UserForm_Initialize()
CommandButton1.Default = True
CommandButton2.Cancel = True
Dim lrow As Long
lrow = Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
month.Value = Range("A" & lrow - 1) + 1
rent.Value = "1220"
netfee.Value = "140"
lastele.Value = Range("E" & lrow - 1)
lastwater.Value = Range("H" & lrow - 1)
Rows("2:2").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=9
Rows(lrow).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & lrow).Select
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton1.BackColor = &H80000016
End Sub
Private Sub CommandButton2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton2.BackColor = &H80000016
End Sub
Private Sub CommandButton3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton3.BackColor = &H80000016
End Sub
Private Sub CommandButton4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton4.BackColor = &H80000016
End Sub
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton1.BackColor = &H8000000F
CommandButton2.BackColor = &H8000000F
CommandButton3.BackColor = &H8000000F
CommandButton4.BackColor = &H8000000F
End Sub
四、 工作簿打开事件代码
Private Sub Workbook_Open()
Userform1.Show False
End Sub
五、 模块代码
Sub 打开窗体()
Userform1.Show False
End Sub