Worksheet_Change: Running when not needed

samuel.nunn

New Member
Joined
Feb 22, 2009
Messages
20
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:
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Rich (BB code):
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

Legend
Joined
Feb 18, 2002
Messages
13,690
Add this as your 1st line of code
Code:
If Target.Count > 1 then Exit Sub
You can also do away with KeyRange and Intersect statements by using
Code:
If Target.Address <> "$C$7" Then Eixt Sub

lenze
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings Sam,

Try:
Code:
    '// 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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,785
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top