Excel VBA数据验证切换仅调试模式生效，触发1004错误求助

我之前也碰到过一模一样的ActiveX控件和数据验证冲突的问题，尤其是那种“调试就好、换机器就好，突然自己炸了”的情况，给你几个针对性的解决方案，按顺序试大概率能搞定：

• 先加个线程同步的小操作
  这个错误很多时候是因为ActiveX控件的点击事件触发后，Excel的UI还没来得及更新控件状态，VBA就急着去操作数据验证了。在代码开头加个DoEvents让UI线程跟上，基本能解决80%的这类问题：

  Private Sub CheckBox1_Click()
      DoEvents ' 让Excel完成控件状态更新等UI操作
      
      Dim cbChecked As Boolean
      cbChecked = Me.OLEObjects("CheckBox1").Object.Value
      
      With Me.Range("E4")
          .ClearContents
          .Validation.Delete
          If cbChecked Then
              .Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=lst_b"
          Else
              .Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=lst_a"
          End If
      End With
  End Sub
  

  这里把ActiveSheet换成Me更稳妥，因为代码是写在工作表模块里的，Me直接指代当前工作表，避免不小心切换工作表导致的对象引用错误。

• 给数据验证操作加错误处理兜底
  有时候单元格可能本来就没有数据验证，执行.Validation.Delete会触发隐性错误，进而影响后续的Add操作。可以加个临时的错误忽略：

  With Me.Range("E4")
      .ClearContents
      On Error Resume Next ' 临时忽略删除不存在的验证的错误
      .Validation.Delete
      On Error GoTo 0 ' 恢复正常错误捕获
      
      If cbChecked Then
          .Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=lst_b"
      Else
          .Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=lst_a"
      End If
  End With
  

• 修复或重建ActiveX控件
  如果你换机器就正常，很大概率是本地Excel的ActiveX控件缓存损坏了。试试这些操作：

  1. 右键点击CheckBox1，选择「控件对象」→「编辑」，随便拖动一下控件再退出编辑模式；
  2. 如果还是不行，直接删除这个CheckBox1，重新插入一个同名的ActiveX复选框，把代码复制过去。

• 禁用事件避免干扰
  要是你的工作簿里还有其他工作表事件或者控件事件，可能会互相干扰。可以在执行操作前后禁用事件：

  Private Sub CheckBox1_Click()
      Application.EnableEvents = False ' 禁用所有事件
      DoEvents
      
      ' 这里放你的核心操作代码
      
      Application.EnableEvents = True ' 恢复事件
  End Sub
  

最后再提一句：这种“时好时坏”的问题，大多和Excel的UI线程与VBA执行线程不同步有关，或者是本地控件的缓存/状态异常，换机器相当于重置了这些状态，所以能正常运行。

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