Suppress a MsgBox in a ChangeEvent

[julhs]

I’ve a msgBox in place to handle a given scenario (it does that)
Unsurprising it is also gets triggered when I MANUALLY clear the range it refers to.
But is there any way to suppress it ONLY when/if I manually clear the range?
Please bear in mind this is very much a work in progress!!
For clarity the "FLtrRng" is the dynamic range that will be filtered and Range("BRangeToFilter").AutoFilter is the NAMED first cell of the filter range.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sht As Worksheet
Dim rng As Range
Dim FrwD As Long
Dim LrwD As Long
Dim FLtrRng As Range

Set sht = ThisWorkbook.ActiveSheet
Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
       FrwD = rng.Row + 2
       LrwD = sht.Cells(sht.Rows.Count, "AT").End(xlUp).Row
'############################################################################
Debug.Print "rng:" & Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Address
Debug.Print "rng: " & rng.Address
Debug.Print "rng.Row: " & rng.Row
Debug.Print "Target: " & Target.Address
Debug.Print LrwD.Address
Debug.Print "LrwD: " & LrwD.Address
Debug.Print "FLtrRng: " & Range("AQ" & FrwD & ":AT" & LrwD).Address
'#############################################################################'

'?????????? Want to suppress this MsgBox ONLY IF the FLtrRng is MANUALY cleared

Set FLtrRng = sht.Range("AQ" & FrwD & ":AT" & LrwD + 1)
       If WorksheetFunction.CountA(FLtrRng) = 0 Then
         MsgBox FLtrRng.Address(0, 0) & " NO Details in Filter Range"
  Else
       If Target.Address = Range("BLimitedFilterList").Address Then
       If Range("BLimitedFilterList") = "All Details" Then
         Range("BRangeToFilter").AutoFilter
  Else
        Range("BRangeToFilter").AutoFilter field:=3, Criteria1:=Range("BLimitedFilterList")
   End If
  End If
 End If
End Sub

 

[Skyybot]

This will work if you have manually selected FLtrRng. You will have to add another End If.

Set FLtrRng = sht.Range("AQ" & FrwD & ":AT" & LrwD + 1)
       If WorksheetFunction.CountA(FLtrRng) = 0 Then
              If Not Selection.Address = FLtrRng.Address Then
                    MsgBox FLtrRng.Address(0, 0) & " NO Details in Filter Range"

 

[julhs]

Thanks Skyybot
Afraid to say that your suggestion seemed to have the same outcome (when MANUALLY clearing FLtrRng) as I was getting with:-

Set FLtrRng = sht.Range("AQ" & FrwD & ":AT" & LrwD + 1)
If WorksheetFunction.CountA(FLtrRng) = 0 Then
MsgBox FLtrRng.Address(0, 0) & " NO Details in Filter Range"

My desire for the MsgBox was because the ChangeEvent sub was going to be called from a CommandButton (in the current screen view area) but the "FLtrRng" was in a different out of view area of the sheet.
But of course I don’t need msgbox to tell me the "FLtrRng" is empty when manually clearing the range, after all I can see the range!
Feel I am over complicating things, so I have decided to completely do away with the MsgBox and deal with the need for it in a different way.
Have dropped all use of "FLtrRng" and MsgBox and replaced it with:-

If Range("BRangeToFilter") = 0 Then
ActiveSheet.AutoFilterMode = False

which prevents the ChangeEvent autofilter from triggering in same way the MsgBox did when "FLtrRng" was manually cleared, BUT without having to close off the msgbox.

Help is always greatly appreciated.
Thank you
Juhls