在Excel中输入固定记录时,会遇到一些重复字段,比如序号,日期;还会因为有些字段是固定的几个,输入不规范会影响后续统计等错做。为此可以设计一个数据录入窗体,以简化数据录入,同时可以规定一些固定字段的值,避免不规范输入。
设计
在VBA环境下创建如下窗体,窗体的下拉菜单和文本框对应需要输入的字段,最下方放置两个按钮,分别用于保存和重置输入内容整体界面设计如下。
在Excel中放入一个按钮控件,并指定宏如下
Sub 启动财务管理系统()
AccountingForm.Show
End Sub
操作
通过窗体录入相应信息,点击保存即可将记录记录进表中。
代码
主体代码如下
Private Sub btnSave_Click()
Sheet2.Activate
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
If emptyRow = 2 Then
Cells(emptyRow, 1).Value = 1
Else
Cells(emptyRow, 1).Value = Cells(emptyRow - 1, 1).Value + 1
End If
Cells(emptyRow, 2).Value = cboAccount.Value
Cells(emptyRow, 3).Value = txtTime.Value
Cells(emptyRow, 4).Value = txtName.Value
Cells(emptyRow, 5).Value = cboDepart.Value
Cells(emptyRow, 6).Value = txtL1.Value
Cells(emptyRow, 7).Value = txtL2.Value
Cells(emptyRow, 8).Value = txtDetail.Value
Cells(emptyRow, 9).Value = txtContract.Value
Cells(emptyRow, 10).Value = txtObject.Value
Cells(emptyRow, 11).Value = txtValue.Value
Cells(emptyRow, 12).Value = txtNote.Value
MsgBox "保存成功"
Init
End Sub
Private Sub btnReset_Click()
Init
End Sub
Private Sub UserForm_Initialize()
Init
End Sub
Public Sub Init()
cboAccount.Value = ""
txtTime.Value = ""
txtName.Value = ""
cboDepart.Value = ""
txtL1.Value = ""
txtL2.Value = ""
txtDetail.Value = ""
txtContract.Value = ""
txtObject.Value = ""
txtValue.Value = ""
txtNote.Value = ""
With cboAccount
.AddItem "中国工商银行"
.AddItem "中国农业银行"
.AddItem "中国银行"
.AddItem "中国建设银行"
.AddItem "交通银行"
.AddItem "中国邮政储蓄银行"
End With
txtTime.Value = Date
With cboDepart
.AddItem "行政"
.AddItem "研发"
.AddItem "市场"
.AddItem "财务"
.AddItem "销售"
End With
End Sub