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"

 

[Artik]

Try it

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

        For Each rng In rngMonitoredRange
            If IsEmpty(rng) Then
                'if there is an empty cell in the changed range, it is presumed to have been cleared,
                'do nothing
            Else
                'The cell in the changed range contains the value,
                'so execute some code
                Stop
            End If
        Next rng
    Else
        'cells other than those being monitored were changed
        'do nothing
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

After reviewing the above schema and when it meets your expectations, the code can be shortened to the form:

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

        For Each rng In rngMonitoredRange
            If Not IsEmpty(rng) Then
                'The cell in the changed range contains the value,
                'so execute some code
                Stop
            End If
        Next rng
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Artik

 

[julhs]

Thanks.
Not going to be able to test run this as have to go out.
But just on a cursory looking over it seems to fit the bill.
Will feed back tomorrow
Julhs

 

[julhs]

Just had to make slightest of adjustments to last couple of lines because as I said; this is just part of a larger Change Event
and when one of the Monitored cells has changed, code needs to continue and carry out the appropriate procedures.
ending is now;

Stop
  End If
 Next rng
Else
  'cells other than those being monitored were changed
  'do nothing
     Application.ScreenUpdating = True
     Application.EnableEvents = True
  Exit Sub
 End If
  ' Sub will now continue with the rest of the Change Event routine
  ' because one of the Monitored cells was changed, so will need to
  ' carry out the appropreate procedures

If it’s any further comfort for your efforts; the point of all this was/is to cut down the size of the whole Change Event.
Not to what it essentially did, but the amount of arguments and counter arguments that I had landed myself with because of my limited VB knowledge/abilities.
By using those few lines I can subsequently eliminate dozens of others, happy days.
If it’s of the remotest interest/relevance, I first attempted to do this here.
Prevent Worksheet Change Event firing when manually editing a cell
Many thanks for your help Artik
Julian