excel - 使用用户表单搜索和编辑记录
问题描述
我对 VBA 比较陌生,并且已经糊涂了,几乎实现了我打算生产的系统。
我现在已经到达一堵砖墙并且已经用头砸了一段时间,我的范式帮助(我确定只有书)将非常感激!
所以我能够搜索记录并从中填充我的用户表单,我想做的是在单击所述用户表单上存在的更新按钮时通过相同的表单更新找到的记录。
Private Sub btsearch_Click()
Dim totrows As Long
totrows = Worksheets("Report").Range("A1").CurrentRegion.Rows.Count
If Txtforename.Text = "" Then
MsgBox "Please enter guest name!!"
End If
For i = 2 To totrows
If Trim(Report.Cells(i, 1)) <> Trim(Txtforename.Text) And i = totrows Then
MsgBox "Guest Not Found"
End If
If Trim(Report.Cells(i, 1)) = Trim(Txtforename.Text) Then
Txtforename.Text = Report.Cells(i, 1)
Txtsurename.Text = Report.Cells(i, 2)
Cboidtype.Text = Report.Cells(i, 3)
txtidnumber.Text = Report.Cells(i, 4)
Cboroomno.Text = Report.Cells(i, 5)
txtcheckin.Text = Report.Cells(i, 6)
txtcheckout.Text = Report.Cells(i, 7)
Cbopaymenttype.Text = Report.Cells(i, 9)
Txttotalpayment.Text = Report.Cells(i, 10)
cmbouser.Text = Report.Cells(i, 11)
Exit For
End If
Next i
End Sub
Private Sub btnupdate_Click()
answer = MsgBox("Would you like to update guest details?", vbYesNo +
vbQuestion, "Update Record")
If answer = vbYes Then
Cells(currentrow, 1) = Txtforename.Text
Cells(currentrow, 2) = Txtsurename.Text
Cells(currentrow, 3) = Cboidtype.Text
Cells(currentrow, 4) = txtidnumber.Text
Cells(currentrow, 5) = Cboroomno.Text
Cells(currentrow, 6) = txtcheckin.Text
Cells(currentrow, 7) = txtcheckout.Text
Cells(currentrow, 9) = Cbopaymenttype.Text
Cells(currentrow, 10) = Txttotalpayment.Text
Cells(currentrow, 11) = cmbouser.Text
End If
End Sub
解决方案
像这样的东西应该可以工作(未经测试)。匹配的行存储在全局变量中,因此您可以在完成编辑时对其进行更新
Dim CurrentRow As Range 'to store the matched row
Private Sub btsearch_Click()
Dim totrows As Long, i As Long, fName
fName = Trim(Txtforename.Text)
If Len(fName) = 0 Then
MsgBox "Please enter guest name!!"
Exit Sub
End If
totrows = Report.Range("A1").CurrentRegion.Rows.Count
Set CurrentRow = Nothing 'clear any previous row
For i = 2 To totrows
If Trim(Report.Cells(i, 1)) = fName Then
Set CurrentRow = Report.Rows(i)
LoadRow CurrentRow '<< save the matched row
Exit For
End If
Next i
If CurrentRow Is Nothing Then
MsgBox "not found!"
End If
End Sub
Private Sub btnupdate_Click()
If MsgBox("Would you like to update guest details?", _
vbYesNo + vbQuestion, "Update Record") = vbYes Then
SaveRow CurrentRow
End If
End Sub
'load a row of data into the userform
Sub LoadRow(rw As Range)
With rw
Txtforename.Text = .Cells(1).Value
Txtsurename.Text = .Cells(2).Value
Cboidtype.Text = .Cells(3).Value
'etc etc
End With
End Sub
'save the userform data back to the sheet
Sub SaveRow(rw As Range)
With rw
.Cells(1).Value = Txtforename.Text
.Cells(2).Value = Txtsurename.Text
.Cells(3).Value = Cboidtype.Text
'etc etc
End With
End Sub
推荐阅读
- mongodb - 在列表中具有对象属性的 MongoDB 查询条目
- node.js - Sequelize 和响应请求 GraphQL
- angular - 如何查找所有 Typescript 项目重新导出的类型
- javascript - 如何使用相互依赖的 github api 执行多次获取并返回数据
- javascript - 用于识别包含在三引号内的有效 Python 字符串的 Javascript 风格正则表达式
- .net - .NET Core 中的动态用户组授权
- c# - SerializationException 和 IOException:在路径上共享冲突
- java - 如何在登录屏幕后面锁定我的 Swagger UI 页面?
- javascript - 如何在javascript中调用页面加载的第一个html元素?
- c# - 如何遍历数据并每隔 n 个条目创建一个新的文本文件