时间戳

首页 > 解决方案 > VBA 用户表单代码仅在工作表可见时才有效

excel - VBA 用户表单代码仅在工作表可见时才有效

问题描述

我确定我刚刚犯了一个男生错误，但我看不到它:(我有一个用户表单可以搜索工作表（表 2）并在列表框中显示结果，但代码只能工作当 excel 可见并且正在搜索的工作表被选中时。任何建议都会得到极大的欢迎 :)

Private Sub Branch_Search_Button_Click()
'branch search
Dim rownum As Long
Dim searchrow As Long

Sheet5.Range("A2:C9999").ClearContents
rownum = 2
searchrow = 2
Do Until Sheet2.Cells(rownum, 1).Value = ""
    If InStr(1, Sheet2.Cells(rownum, 2).Value, TextBox1.Value, vbTextCompare) > 0 Then
        Sheet5.Cells(searchrow, 1).Value = Cells(rownum, 1).Value
        Sheet5.Cells(searchrow, 2).Value = Cells(rownum, 2).Value
        Sheet5.Cells(searchrow, 3).Value = Cells(rownum, 3).Value
        searchrow = searchrow + 1
    End If
    rownum = rownum + 1
Loop

If searchrow = 2 Then
    MsgBox "Area not found"
    Exit Sub
End If

ListBox2.RowSource = "Area_Search!a1:c" & Range("c" & Rows.Count).End(xlDown).Row
End Sub

就是这样！感谢您的帮助，超级对称真的很感激！！！:D

标签： excelvbauserform

解决方案

潜在的解决方案

您应该完全限定您的所有范围。以下可能会解决您的错误。请注意以开头的评论'*

Private Sub Branch_Search_Button_Click()
'branch search
Dim rownum As Long
Dim searchrow As Long

    Sheet5.Range("A2:C9999").ClearContents
rownum = 2
searchrow = 2
Do Until Sheet2.Cells(rownum, 1).Value = ""
    If InStr(1, Sheet2.Cells(rownum, 2).Value, TextBox1.Value, vbTextCompare) > 0 Then
        '* Change Sheet2 to the appropriate sheet code
        Sheet5.Cells(searchrow, 1).Value = Sheet2.Cells(rownum, 1).Value
        Sheet5.Cells(searchrow, 2).Value = Sheet2.Cells(rownum, 2).Value
        Sheet5.Cells(searchrow, 3).Value = Sheet2.Cells(rownum, 3).Value
        searchrow = searchrow + 1
    End If
    rownum = rownum + 1
    Loop

If searchrow = 2 Then
MsgBox "Area not found"
Exit Sub
End If

'* change Sheet5 to the appropriate sheet code
ListBox2.RowSource = "Area_Search!a1:c" & Sheet5.Range("c" & Rows.Count).End(xlDown).Row
End Sub

推荐阅读