Worksheet_Change: Running when not needed

[samuel.nunn]

My Worksheet_Change macro is running when I don't need it to run. When cell C7 is modified, I want it to delete the modification and display the message "You cannot modify this cell."

It works properly if someone tries to modify that cell. However, it also triggers if someone selects multiple cells (including C7) and hits delete. I don't want it to trigger in this case. After all, there will be nothing in that cell to delete anyways. Is there a workaround? Or at least a way for the message box to not pop up in this case?

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("C7")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        
        
        
        Application.EnableEvents = False
        Range("C7").Clear
        Range("C8").Select
        MsgBox "You cannot modify this cell."
        
        Application.EnableEvents = True
            
       
    End If
End Sub

Sam

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("C7")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        
        
        
        Application.EnableEvents = False
        Range("C7").Clear
        Range("C8").Select
        MsgBox "You cannot modify this cell."
        
        Application.EnableEvents = True
            
       
    End If
End Sub

 

[lenze]

Add this as your 1st line of code

If Target.Count > 1 then Exit Sub

You can also do away with KeyRange and Intersect statements by using

If Target.Address <> "$C$7" Then Eixt Sub

lenze

 

[GTO]

Greetings Sam,

Try:

    '// Target is a Range, just use Target.//
    If Not Application.Intersect(KeyCells, Target) Is Nothing _
    And Not Target.Count > 1 Then

Hope this helps,

Mark