CODEWITHSUNDEEP
vbaexcel
Jamie Sutton
Jamie Sutton

Reputation: 163

VBA Validation is crashing my workbook

I have VBA code to alter validation on a cell dependant on the option selected.

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

It works while the workbook is open. It functions fine, no errors or anything. However, when I save and reopen the workbook, it gives me the following:

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.

It then opens the workbook, strips all my formatting and removes the VBA.

I've tried Googling but haven't been able to translate that to what I've got.

Cheers,

Upvotes: 1

Views: 264

Answers (1)

user6432984
user6432984

Reputation:

Before changing values on the ActiveSheet using the Worksheet_Change you need to turn off the events. This prevents the Worksheet_Change from triggering again and possibly causing an infinite loop. Make sure that you turn them back on before the event exits.

It is good practice to add an error handler so that if something does go wrong Events will automatically be turned back on. 

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

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

Upvotes: 3

Related Questions