一、任务描述

通过窗体实现数据的较便捷录入，如下：

二、 窗体相关基础知识

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