VBA 用户窗体查找功能显示记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18176700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Userform Find function display records
提问by Matt W
I'm in the process of making a userform. I have managed to setup a find function using the code below which then also loops and counts the number of cases in the spreadsheet.
我正在制作用户表单。我设法使用下面的代码设置了一个查找函数，然后它还会循环并计算电子表格中的案例数。
I have also created a function to find the next item which is operated by a separate command button but it does not display the records in the userform so it can be amended.
我还创建了一个函数来查找由单独的命令按钮操作的下一个项目，但它不会在用户表单中显示记录，因此可以对其进行修改。
Does anyone have any ideas on how to fix this?
有没有人对如何解决这个问题有任何想法？
Private Sub FindNext_Click()
Cells.FindNext(After:=ActiveCell).Activate
End Sub
Private Sub Find_Click()
Worksheets("Master").Activate
Dim strFind As String
Dim FirstAddress As String
Dim rSearch As Range
Set rSearch = Range("a1", Range("e65536").End(xlUp))
Dim f As Integer
strFind = Me.TextBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then
c.Select
With Me
.TextBox2.Value = c.Offset(0, 1).Value
.TextBox3.Value = c.Offset(0, 2).Value
.TextBox4.Value = c.Offset(0, 3).Value
.TextBox5.Value = c.Offset(0, 4).Value
.TextBox6.Value = c.Offset(0, 5).Value
.TextBox7.Value = c.Offset(0, 6).Value
.TextBox8.Value = c.Offset(0, 7).Value
.TextBox9.Value = c.Offset(0, 8).Value
.TextBox10.Value = c.Offset(0, 9).Value
.TextBox11.Value = c.Offset(0, 10).Value
.TextBox12.Value = c.Offset(0, 11).Value
.TextBox13.Value = c.Offset(0, 12).Value
.TextBox14.Value = c.Offset(0, 13).Value
.TextBox20.Value = c.Offset(0, 14).Value
.TextBox21.Value = c.Offset(0, 15).Value
.TextBox15.Value = c.Offset(0, 16).Value
.TextBox22.Value = c.Offset(0, 17).Value
.TextBox16.Value = c.Offset(0, 18).Value
.TextBox18.Value = c.Offset(0, 19).Value
.TextBox19.Value = c.Offset(0, 20).Value
.update.Enabled = True
.Add.Enabled = False
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
Case vbOK
Case vbCancel
End Select
Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed"
End If
End With
End Sub
Thanks
谢谢
回答by Cor_Blimey
You need to encapsulate the update codeinto its own method (sub) then you can call it for both Find and Find Next. Like:
您需要将更新代码封装到它自己的方法（子）中，然后您可以为 Find 和 Find Next 调用它。喜欢：
Private Sub FindNext_Click()
Dim nextCell As Range
Set nextCell = Cells.FindNext(After:=ActiveCell)
'FindNext loops round to the initial cell if it finds no other so we test for it
If Not nextCell.Address(external:=true) = ActiveCell.Address(external:=true) Then
updateFields anchorCell:=nextCell
End If
End Sub
Private Sub Find_Click()
Worksheets("Master").Activate
Dim strFind As String
Dim FirstAddress As String
Dim rSearch As Range
Set rSearch = Range("a1", Range("e65536").End(xlUp))
Dim f As Integer
strFind = Me.TextBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then
updateFields anchorCell:=c
FirstAddress = c.Address
Do
f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
Case vbOK
Case vbCancel
End Select
Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed"
End If
End With
End Sub
Private Sub updateFields(anchorCell As Range)
anchorCell.Select
With Me
.TextBox2.Value = anchorCell.Offset(0, 1).Value
.TextBox3.Value = anchorCell.Offset(0, 2).Value
.TextBox4.Value = anchorCell.Offset(0, 3).Value
.TextBox5.Value = anchorCell.Offset(0, 4).Value
.TextBox6.Value = anchorCell.Offset(0, 5).Value
.TextBox7.Value = anchorCell.Offset(0, 6).Value
.TextBox8.Value = anchorCell.Offset(0, 7).Value
.TextBox9.Value = anchorCell.Offset(0, 8).Value
.TextBox10.Value = anchorCell.Offset(0, 9).Value
.TextBox11.Value = anchorCell.Offset(0, 10).Value
.TextBox12.Value = anchorCell.Offset(0, 11).Value
.TextBox13.Value = anchorCell.Offset(0, 12).Value
.TextBox14.Value = anchorCell.Offset(0, 13).Value
.TextBox20.Value = anchorCell.Offset(0, 14).Value
.TextBox21.Value = anchorCell.Offset(0, 15).Value
.TextBox15.Value = anchorCell.Offset(0, 16).Value
.TextBox22.Value = anchorCell.Offset(0, 17).Value
.TextBox16.Value = anchorCell.Offset(0, 18).Value
.TextBox18.Value = anchorCell.Offset(0, 19).Value
.TextBox19.Value = anchorCell.Offset(0, 20).Value
.Update.Enabled = True
.Add.Enabled = False
End With
End Sub
