CODEWITHSUNDEEP
excelvba
Filcuk
Filcuk

Reputation: 184

Workbook_SheetChange event triggers twice when it shouldn't

Below code that was in use for a while isn't working anymore.

I did a test in new sheet without other code with the same result.

We've recently moved to Office 365 and my current Excel version is 1902.
Unfortunately everyone here has the same version now, so I can't test it on an older one.
I mention this because I can't think of anything but this being due to a new bug?

Edit: I should add that this was used to prevent (re)moving rows or columns.

Edit: What doesn't work: It triggers twice every time. (I left this important part out after several edits)

Private Sub Workbook_SheetChange(ByVal wks As Object, ByVal Target As Range)

    If ((Target.Address = Target.EntireRow.Address Or _
    Target.Address = Target.EntireColumn.Address)) Then

        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            MsgBox "Do not modify the structure.", vbExclamation, "Notice"
        End With

    End If

End Sub

Upvotes: 0

Views: 143

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you could try and turn it into a SelectionChange event

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If ((Target.Address = Target.EntireRow.Address Or _
    Target.Address = Target.EntireColumn.Address)) Then

        With Application
            .EnableEvents = False
            Target.Cells(1, 1).Select
            .EnableEvents = True
        End With
        MsgBox "Do not modify the structure.", vbExclamation, "Notice"

    End If

End Sub

that would preserve you from Undo usage and all its consequences

Upvotes: 1

Related Questions