Determine if Clear Contents was selected

[julhs]

I have a long Change Event routine that I’m trying to prevent from running in its entirety; given a couple of specific scenarios.

Essentially I’m trying to limit how much of the Change Event is run when just ANY random cell on the sheet is changed by editing or entering a new value.
I currently have the below code in the Change Event (which just utilises some reverse logic) in that the Change Event will EXIT if the ACTIVE CELL is NOT one of the ones declared (just ANY random one).
If the active cell IS one of the declared cells, then the WHOLE Change Event will run in its entirety.

On testing things my code does appear to do what I was initually intending.
HOWEVER; the code is floored IF I utilise the right click mouse option and use “Clear Contents”

I’ve tried hard to make/create a reference the right click mouse option, “Clear Contents” and exit the routine but I simply can’t get the syntax required.

'All variables have been previously declared
   'Eg. Dim BTgt1 As Range,  Dim FrwD As Long
   'Set BTgt1 = Range("AU" & FrwD - 2)
   '   etc
'preceding code here:……….
If (ActiveCell = BTgt1 Or ActiveCell = BTgt2 Or ActiveCell = BTgt3 Or ActiveCell =BTgt4) Then
Range("au" & FrwD).Select
Exit Sub
Else
'Code continues with the rest of the Change Event routine

So I'm after the syntax required to reference the right mouse click for "Clear Contents"

 

[Micron]

If you still want to allow right clicks on the sheet (as opposed to disabling altogther) I'm thinking this could be as simple as using BeforeRightClick to set a module level variable. The Change event will fire after that, and the code could do whatever you want based on the variable value. So that's two options for you.

While it wouldn't tell you if the menu item was chosen, if the cell is empty you can assume it was used?

 

[Artik]

“Clear Contents” is more or less the same as using the Delete key. Both ways clear the cell. The question then becomes what do you actually want to achieve? Don't you want to allow data to be deleted from the active cell regardless of the deletion method?

Artik

 

[StephenCrump]

Further to @Artik's comments, with Worksheet_Change code, you need to allow for Target to have more than one cell (e.g. because a user might select a multi-cell range and clear contents).

Here's how you'd check for possible changes to one or more of your four cells (which will include the user clearing contents):

If Not Intersect(Target, Union(BTgt1, BTgt2, BTgt3, BTgt4)) Is Nothing Then
    'Changes to one or more of your monitored cells
    'Do something here ....

End If

If you don't need to do anything on clear contents, you could simply check the target cell(s) for blank values?

BTW, your test below won't work:

If (ActiveCell = BTgt1 Or ActiveCell = BTgt2 Or ActiveCell = BTgt3 Or ActiveCell =BTgt4) Then

A range's default property is .Value, so this is actually testing: If ActiveCell.Value = BTgt1.Value etc ..

 

[Rick Rothstein]

Maybe you can declare a global module Boolean variable (outside of any routine) and then set it to True in the SelectionChange event if it is one of the 4 cells you are monitoring, then in the Change event, you can move to FrwD and Exit the Change if that global variable is set to True, otherwise do whatever it is you want to do in Change for any other cells.

 

[julhs]

My profuse apologise everyone!!
I my haste to post before I went out, I cut and pasted the WRONG piece of code which will have effected your replies.
But can I just restate; "I'm after the syntax required to reference the right mouse click for "Clear Contents"
Or any other method to overcome the quirk of my Exit Sub section in the change event seeing the use of "Right mouse Click >> Clear Contents" as a change to one of the 4 mentioned ranges when used on any other random cell on the sheet.
Some further info:
BTgt1 & BTgt1 are ordinary Validation lists, GetSubList is an ActiveX Validation lists, all are single cell Named Ranges used as the link cells for the Change Event to call the appropriate Sub.
UsedRangeA is a name range cell that just holds the result of a separately run Sub.

What I said in post#1 all remains the same, just the snippet of code supplied was wrong one, below is the correct piece

If Not (ActiveCell = BTgt1 Or ActiveCell = BTgt2 _
 Or ActiveCell = GetSubList Or ActiveCell = UsedRangeA) Then
  Range("au434").Select
   Application.ScreenUpdating = True
   Application.EnableEvents = True
 Exit Sub
Else

 

[julhs]

Thanks everyone for the input, have a bit to look into.

Micron; never used it, will have a play around with it.

Artik; my updated code does work to Exit the change event when ANY cell value OTHER than those mentioned are changed. If one of those cells are changed (via Validation List) the change event needs to and does run in its entirety. My dilemma is IF I use “Right Click >> Clear Contents” on a cell somewhere on sheet (OTHER than those mentioned) the change event will skip the section to Exit and just runs in its entirety.

Stephan; I’m using

If Not (ActiveCell = BTgt1 etc etc

because not looking to test for a value, only that the ActiveCell is the Named Range “BTgt1”

Rick; I’ll have a look into your suggestion

 

[julhs]

Have updated previous code with small addition that seems to kick in when I use the "Right Click >> Clear Contents". I’ve done some
preliminary test runs under few different scenarios and initially seems to do the trick, BUT will have to do some more extensive ones to be sure is OK.
Can anyone see a big problem with using it?

' >> preceding Change Event code here:……….
If Not (ActiveCell = BTgt1 Or ActiveCell = BTgt2 _
   Or ActiveCell = GetSubList Or ActiveCell = UsedRangeA) Then
    Application.ScreenUpdating = True
   Application.EnableEvents = True
 Exit Sub 'the full Change Event
End If

If ActiveCell = "" Then 'seems to cater for if "Right Click >> Clear Contents" used
    Application.ScreenUpdating = True
   Application.EnableEvents = True
 Exit Sub 'the full Change Event
Else
' >> Code continues with the rest of the Change Event routine

 

[Artik]

Stephan; I’m using

If Not (ActiveCell = BTgt1 etc etc

because not looking to test for a value, only that the ActiveCell is the Named Range “BTgt1”

That's the problem: you're just doing with code what you don't want to do. This code is comparing values, not determining whether these are monitored cells.

Assuming BTgt1, BTgt2, GetSubList, UsedRangeA are cell references, analyze the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    '(...)
    
    Dim rngMonitoredRange As Range
    Dim rng As Range

    Set rngMonitoredRange = Union(BTgt1, BTgt2, GetSubList, UsedRangeA)
    
    If Not Intersect(Target, rngMonitoredRange) Is Nothing Then
        'One of the monitored cells has been changed, so quit the procedure
        Me.Range("AU434").Select
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    Else
        'cells other than those being monitored were changed
        
        For Each rng In Target
            If IsEmpty(rng) Then
                'if there is an empty cell in the changed range, it is presumed to have been cleared,
                'so quit the procedure
                
                Me.Range("AU434").Select
                Application.ScreenUpdating = True
                Application.EnableEvents = True
                Exit Sub
            Else
                'The cell in the changed range contains the value,
                'so execute some code
                Stop
            End If
        Next rng
    End If
    
    
End Sub

Artik

 

[julhs]

Hi Artik
I appreciate your input.
But excuse me for saying I believe what you’re saying and given code for is not right for what I’m trying to do, rather the reverse of it.
Firstly forget about the part,

Me.Range("AU434").Select

But,

If Not Intersect(Target, rngMonitoredRange) Is Nothing Then
'One of the monitored cells has been changed, so quit the procedure

Is wrong because if a MONITORED cell has been changed it NEEDS to continue NOT Exit
Only if it’s an UNMONITORED cell, should it EXIT as my whole Change Event code is in the region of 200 lines in length, if an Unmonitored cell is changed 195 of those lines are irrelevant so don’t need to run
My original code did all this; HOWEVER if I used the "Right Click >> Clear Contents" on an UNMONITORED cell the event code skipped the Exit line and the whole rest of change event code would run unnecessarily.