Excel VBA点击全量数据切换按钮时.Find方法失效问题求助

问题说明 #

工作表包含5个切换按钮：4个用于拉取另一工作表的部分数据，第5个用于拉取全量数据。

• 点击部分数据按钮时，会将全量按钮设为False，再调用带参数的SearchData子过程，运行正常。
• 点击全量按钮时，若取消注释将另外4个按钮设为False的语句，SearchData子过程中的.Find方法会失效，所有返回值为空，触发Run-Time error '1004'；仅当部分数据按钮原本为True时会出现此问题，若原本就是False则运行正常。

相关代码 #

全量按钮点击事件 #

Private Sub TbAllOpen_Click()

    If TbAllOpen.value = True Then
        TbAllOpen.BackColor = &HFF00&
'        TbOpenEst.value = False
'        TbOpenInv.value = False
'        TbReceivables.value = False
'        TbUnsuc.value = False
        CurrentStatus = "All"
        SearchData CurrentStatus
    Else
        TbAllOpen.BackColor = &HFFE1C8
    End If

End Sub

部分数据按钮示例（其余3个逻辑一致） #

Private Sub TbOpenEst_Click()

    If TbOpenEst.value = True Then
        TbOpenEst.BackColor = &HFF00&
        TbAllOpen.value = False
        CurrentStatus = "Quote"
        SearchData CurrentStatus
    Else
        TbOpenEst.BackColor = &HFFE1C8
    End If
    
    SendKeys ("{ESC}")

End Sub

SearchData子过程中失效的代码段 #

Dim L as Long

L = Ws1.Cells(1, Ws1.Columns.Count).End(xlToLeft).Column

With Ws1.Range("A1", Ws1.Cells(1, L))

    Dim FindResult As Range
    
    Set FindResult = .Find("Active", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld2 = FindResult.Column
    End If

    Set FindResult = .Find("Date", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld3 = FindResult.Column
    End If
    
    Set FindResult = .Find("Status", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld6 = FindResult.Column
    End If
    
    Set FindResult = .Find("generated_job_id", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld10 = FindResult.Column
    End If
    
    Set FindResult = .Find("payment_amount", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld16 = FindResult.Column
    End If
    
    Set FindResult = .Find("total_invoice_amount", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld36 = FindResult.Column
    End If
    
    Set FindResult = .Find("category_uuid", LookAt:=xlWhole)
    If Not FindResult Is Nothing Then
        Fld38 = FindResult.Column
    End If
    
End With

问题原因与解决办法 #

原因分析 #

通过代码修改切换按钮的value属性时，会触发对应按钮的Click事件。比如把TbOpenEst.value设为False时，会执行TbOpenEst_Click()的Else分支，其中的SendKeys ("{ESC}")会干扰Excel的焦点状态，导致后续Find方法无法正常定位单元格。

解决办法 #

方法1：临时禁用事件触发

在修改按钮状态前关闭Excel事件，操作完成后重新开启，避免触发不必要的按钮点击事件：

Private Sub TbAllOpen_Click()

    If TbAllOpen.value = True Then
        TbAllOpen.BackColor = &HFF00&
        
        ' 禁用事件，防止修改按钮值触发其他Click事件
        Application.EnableEvents = False
        TbOpenEst.value = False
        TbOpenInv.value = False
        TbReceivables.value = False
        TbUnsuc.value = False
        ' 重新启用事件
        Application.EnableEvents = True
        
        CurrentStatus = "All"
        SearchData CurrentStatus
    Else
        TbAllOpen.BackColor = &HFFE1C8
    End If

End Sub

方法2：优化按钮事件逻辑

新增全局变量标记是否为代码触发的状态变更，避免执行不必要的代码：

' 模块级别声明全局变量
Dim IsCodeChanging As Boolean

' 全量按钮事件
Private Sub TbAllOpen_Click()
    If TbAllOpen.value = True Then
        TbAllOpen.BackColor = &HFF00&
        IsCodeChanging = True
        TbOpenEst.value = False
        TbOpenInv.value = False
        TbReceivables.value = False
        TbUnsuc.value = False
        IsCodeChanging = False
        CurrentStatus = "All"
        SearchData CurrentStatus
    Else
        TbAllOpen.BackColor = &HFFE1C8
    End If
End Sub

' 部分数据按钮事件修改
Private Sub TbOpenEst_Click()
    If IsCodeChanging Then Exit Sub ' 代码触发的变更直接退出
    
    If TbOpenEst.value = True Then
        TbOpenEst.BackColor = &HFF00&
        IsCodeChanging = True
        TbAllOpen.value = False
        IsCodeChanging = False
        CurrentStatus = "Quote"
        SearchData CurrentStatus
    Else
        TbOpenEst.BackColor = &HFFE1C8
        SendKeys ("{ESC}") ' 仅手动点击取消时执行SendKeys
    End If
End Sub

方法3：替换不必要的SendKeys

如果SendKeys仅用于消除按钮焦点，可直接设置焦点到单元格替代：

Private Sub TbOpenEst_Click()
    If TbOpenEst.value = True Then
        TbOpenEst.BackColor = &HFF00&
        TbAllOpen.value = False
        CurrentStatus = "Quote"
        SearchData CurrentStatus
    Else
        TbOpenEst.BackColor = &HFFE1C8
        ' 替换SendKeys，设置焦点到A1单元格
        Ws1.Range("A1").Select
    End If
End Sub

内容的提问来源于stack exchange，提问作者Hareborn