CODEWITHSUNDEEP
excelvba
Rbon
Rbon

Reputation: 152

Worksheet_SelectionChange inappropriately being fired

I have a named range which I want to keep safe from user modifications, so I use a basic Worksheet_Change routine:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    If Not Intersect(Target, Range("MyRange")) Is Nothing Then
        Application.Undo
        MsgBox "You're not allowed to do this!"
    End If
    Application.EnableEvents = True

End Sub

This was the first routine I wrote for my woksheet, and it seems to work just fine. Whenever a user tries to delete or change some cell within the named range, the event triggers and the change made is reversed. Also the message box appears: "You're not allowed to do this!".

However, I need to alert the user if he selects a cell within the range he is not allowed to enter data. For that I use Worksheet_SelectionChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("MyRange")) Is Nothing Then
        MsgBox "This cells is protected."
    End If

End Sub

The Worksheet_SelectionChange works if I simply go selecting different cells. I mean, the event triggers as it should, and the message "This cells is protected." shows up as expected. The problem is when I change the value of some cell within the named range. Both events are being fired. It shows "You're not allowed to do this!" and then "This cells is protected."

How do I prevent the Worksheet_SelectionChange from firing after the Worksheet_Change runs?...

Edit: I forgot to explain something. When I select some "protected cell" the Worksheet_SelectionChange fires. If I simply delete that very cell's content then Worksheet_Change fires as expected and nothig else happens. As expected.

Upvotes: 3

Views: 128

Answers (2)

Rbon
Rbon

Reputation: 152

I've just realized that when you delete a cell and then change the selection, the Worksheet_SelectionChange will exit after the first IF statement.

I could solve this with an IF statement in the Worksheet_Change routine (immediately before Application.Undo):

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("MyRange")) Is Nothing Then

        If Target.Value = "" Then
            runTHis = True
        Else
            runTHis = False
        End If

        Application.Undo
        MsgBox "You're not allowed to do this!"
    End If
    Application.EnableEvents = True
End Sub

Besides, I made runTHis a global variable, cause it needed to be iniatialized as TRUE, within a Workbook_Open routine.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

Is this what you are trying?

Option Explicit

Dim runThis As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If runThis = False Then
        runThis = True
        Exit Sub
    End If
    
    If Not Intersect(Target, Range("MyRange")) Is Nothing Then
        MsgBox "This cells is protected."
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("MyRange")) Is Nothing Then
        Application.Undo
        MsgBox "You're not allowed to do this!"
        runThis = False
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 3

Related Questions