用Excel VBA代码实现去重录入某字段内容
来源:互联网 发布:网吧告计费软件 编辑:程序博客网 时间:2024/05/18 02:38
功能描述
图1 信息录入表单示意图
图2 用于录入信息的自定义窗体示意图
如图所示,在样表中用自定义窗体录入信息,要求:
1、日期自动设为当前日期,不用手动录入;
2、车牌号不能重复录入(之前重复的不作考虑),否则停止运行,并弹出消息;
要点分析
1、实现功能1要点
禁用日期文本框,当前日期用Format(Date, "yyyy/m/d")获取
2、实现功能2要点
(1)查找车牌号:用for-each遍历,若找到,则转至(2);否则转至(4)
(2)中断循环,给出提示:用MsgBox;
(3)再选中该车牌号文本:用text.SelStart和text.SelLength;转到(6)
-------------------
(4)在新的一行录入信息:新行标用Range("A65536").End(xlUp).Row+1
(5)录入文本框内容清理;
(6)退出录入过程;
其他组件
1、主窗体fmMain
在打开Excel或选中Sheet2时显示该主页面:
2、查询窗体fmQuery
查询车牌号,结果列在Sheet3中:
完整代码
1、录入窗体fmImput代码:
Option ExplicitPrivate Sub cmdSave_Click() '非空验证 If txtDate.Value = "" Or txtUserName.Value = "" Or txtUserCarNo.Value = "" _ Or txtUserTel.Value = "" Or txtUserCarType.Value = "" Then MsgBox "信息录入不完整,请补充完整后再保存!", vbCritical, "录入错误" txtUserName.SetFocus Exit Sub End If '车牌号去重验证 Dim carID As String: carID = txtUserCarNo.Text Dim REPEATED As Boolean: REPEATED = False Dim cell As Range For Each cell In Sheet1.Columns("B:B").Cells If cell.Value = carID Then REPEATED = True Exit For End If Next '未通过验证 If REPEATED Then MsgBox "您当前录入车牌号[" + carID + "]已被其他用户录入,请重新输入!", vbCritical, "车牌号重复" REPEATED = False txtUserCarNo.SetFocus txtUserCarNo.SelStart = 0 txtUserCarNo.SelLength = Len(carID) Exit Sub End If '通过验证 Application.ScreenUpdating = False Sheet1.Activate Dim newRow As Integer newRow = Sheet1.Range("A65536").End(xlUp).Row + 1 Cells(newRow, 1).Value = txtDate.Text Cells(newRow, 2).Value = txtUserCarNo.Value Cells(newRow, 3).Value = txtUserName.Value Cells(newRow, 4).Value = txtUserTel.Value Cells(newRow, 5).Value = txtUserCarType.Value MsgBox "用户信息保存成功,单击【确定】继续!", vbInformation, "操作成功" txtUserCarNo.Value = "" txtUserName.Value = "" txtUserTel.Value = "" txtUserCarType.Value = "" Application.ScreenUpdating = TrueEnd SubPrivate Sub cmdBack_Click() fmInput.Hide Sheet2.ActivateEnd SubPrivate Sub UserForm_Initialize() txtDate.Text = Format(Date, "yyyy/m/d") txtDate.Enabled = False txtUserCarNo.Value = "" txtUserName.Value = "" txtUserTel.Value = "" txtUserCarType.Value = ""End Sub
2、主窗体fmMain代码:
Private Sub cmdAddUserInfo_Click() Sheet1.Activate fmMain.Hide fmInput.ShowEnd SubPrivate Sub cmdQuery_Click() Sheet3.Activate fmMain.Hide fmQuery.ShowEnd Sub
3、查询车牌窗体fmQuery代码:
Private Sub cmdQuery_Click() '非空验证 If txtTargetCarID.Value = "" Then MsgBox "要查询的车牌号错误或为空值", vbCritical, "输入错误" txtTargetCarID.SetFocus Exit Sub End If Application.ScreenUpdating = False Sheet1.Activate '获取数据源区域和查询条件 Dim carID As String: carID = txtTargetCarID.Text Dim lastRow As Integer: lastRow = Range("A65536").End(xlUp).Row Set sourceArea = Range(Cells(2, 1), Cells(lastRow, 5)) '获取匹配记录总数 Dim cell As Range Dim resultCount As Integer For Each cell In Sheet1.Range("B2:B" & lastRow) If cell.Value = carID Then resultCount = resultCount + 1 End If Next '无记录则退出查询 Dim info As String If resultCount = 0 Then info = "操作失败!" & vbCrLf & "没有找到车牌号为[ " & carID & " ]的用户信息,请核对车牌号后重试!" MsgBox info, vbCritical, "查询结果" txtTargetCarID.SetFocus txtTargetCarID.SelStart = 0 txtTargetCarID.SelLength = Len(carID) Exit Sub End If '有记录则循环输出查询结果 Dim resultArea() ReDim resultArea(1 To resultCount, 1 To 5) Dim sourceRow As Integer Dim resultRow As Integer For sourceRow = 1 To sourceArea.Rows.Count If sourceArea.Item(sourceRow, 2).Value = carID Then resultRow = resultRow + 1 For i = 1 To 5 resultArea(resultRow, i) = sourceArea(sourceRow, i) Next i i = 0 End If Next Sheet3.Activate Range("A2:E65536").ClearContents Range("A2:E5").Resize(resultCount) = resultArea info = "操作成功!" & vbCrLf & "共查询到" & resultCount & "条车牌号为[" & carID & "]的用户信息!" MsgBox info, vbInformation, "查询结果" txtTargetCarID.Text = "" txtTargetCarID.SetFocus Application.ScreenUpdating = True End SubPrivate Sub cmdCancel_Click() fmQuery.Hide Sheet2.ActivateEnd Sub
运行结果:
(1)录入重复车牌号时:
(2)录入不重复车牌时:
(3)查询到已有车牌时:(多条记录)
(4)未查询到结果时:
要点小结
1、命名统一采用“控件简称+描述性名称”(如txtDate、cmdSave等)的方式,便于后期维护与更新;
2、选中文本框中文本的方法:
txtUserCarNo.SetFocus txtUserCarNo.SelStart = 0 txtUserCarNo.SelLength = Len(carID)
3、获取工作表中整列区域:
Sheet1.Columns("B:B").Cells4、获取当前区域的最后一行行标:
Sheet1.Range("A65536").End(xlUp).Row
5、格式化当前时间:
Format(Date, "yyyy/m/d")
6、初始化窗体的控件事件不能使用自定义名称:
正确:
Private Sub UserForm_Initialize() ...End Sub
错误:
Private Sub fmInput_Initialize() ...End Sub
7、命令按钮快捷键设置:用Accelerator属性
8、使用动态数组节约内存资源:
Dim resultArea() ReDim resultArea(1 To resultCount, 1 To 5)
0 0
- 用Excel VBA代码实现去重录入某字段内容
- 用EXCEL实现三级联动的vba代码
- js实现去重代码
- list<Dto>根据某字段去重
- 按照某字段去重的SQL
- 使用VBA实现Excel合并相同内容的相邻单元格
- VBA陈旧的代码:在VBA中操作Excel内容一
- VBA陈旧的代码:在VBA中操作Excel内容二
- 去字段中的内容
- 用VBA宏代码破解Excel密码保护
- 经典Excel VBA代码
- 经典Excel VBA代码
- Excel VBA代码学习
- 调试Excel VBA代码
- Excel VBA 代码笔记
- VBA代码拆分excel
- 怎么样用java代码去实现文件导出到Excel
- 用VBA实现Excel中某单元格不能为空
- 访问Docker容器有哪些方法?
- php面试问题
- 基于Java Socket的自定义协议,实现Android与服务器的长连接(一)
- bzoj3998 [TJOI2015]弦论
- centos7启动Genymotion时报错,`CXXABI_1.3.8' not found和`GLIBCXX_3.4.20' not found,解决方案
- 用Excel VBA代码实现去重录入某字段内容
- Oracle 12c JDBC 连接
- POJ 1265 Area
- Android-startActivityForResult用法
- iOS 抽屉效果实现
- 两数之和(二)
- 火狐浏览器打开百度网页时,提示"您的连接不安全"怎么办
- Golang编程经验
- lightoj1062【几何(二分)】