VBA 验证使我的工作簿崩溃

Question

我有 VBA 代码来更改依赖于所选选项的单元格的验证。

Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Set ws = Sheets("lkup")

    Dim VariationList As Variant
    VariationList = Application.Transpose(ws.Range("Resource_List"))
    For i = LBound(VariationList) To UBound(VariationList)

    Next i
        If Target = Range("B15") Then
        If InStr(1, Range("B15"), "Resource") > 0 Then
            With Range("D15").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlEqual, Formula1:=Join(VariationList, ",")
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        ElseIf InStr(1, Range("B15"), "Fixed Asset") > 0 Then
            Range("D15").Validation.Delete
            Range("D15").ClearContents
            With Range("D15").Validation
                .Delete
                .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="100000", Formula2:="999999"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Oopps"
                .InputMessage = ""
                .ErrorMessage = "Your fixed asset number can only be 6 numbers"
                .ShowInput = True
                .ShowError = True
            End With
        Else
            Range("D15").ClearContents
            Range("D15").Validation.Delete
        End If
    End If
End Sub

它在工作簿打开时工作。它运行良好，没有错误或任何东西。但是，当我保存并重新打开工作簿时，它给了我以下信息:

We found a problem with some content in 'Invoice.xlsm'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

然后它会打开工作簿，删除我的所有格式并删除 VBA。

我试过谷歌搜索，但无法将其翻译成我所拥有的。

干杯，

Answer 1

在使用 Worksheet_Change 更改 ActiveSheet 上的值之前您需要关闭事件。这可以防止 Worksheet_Change再次触发并可能导致无限循环。确保在事件退出之前重新打开它们。

添加错误处理程序是一种很好的做法，这样如果出现问题，事件将自动重新打开。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo ResumeEvents
    Application.EnableEvents = False

    '----{Code}------
ResumeEvents:
    Application.EnableEvents = True
End Sub