specialcells triggers selection change.  how can selectionchange detect this abomination?

This has been modified since I originally posted it.  The new comments are at the end.

I have just discovered that specialcells sometimes triggers the selectionchange routine.  

This is really screwy and causing me problems.  I have maybe 100 macros that use specialcells, and I don't want them all to go bonkers when my addin is active.

Nor do I want to go to all 100 routines and add .enableevents = false then reset back to true after specialcells

I think the following workaround is OK. it seems to solve my problems, but I am worried that there might be some obscure details I am missing.

Can anybody enlighten me on this issue? I am only looking for general guidelines, so I am intentionally not giving much detail about my addin.
For instance, under what other conditions might target.address not be the same as selection.address???

sub  MyApplication_SheetSelectionChange(ByVal Sh As Object, ByVal target As Range)
' see http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23511653.html
if typename(target) is nothing then exit sub  ' <==== the typname is just in case the selected cells  have all been deleted
' the following is just in case the selectionchange routine gets called by specialcells or similar abominations
Dim cntWeird As Long
If Intersect(ActiveCell, target) Is Nothing Then cntWeird = cntWeird + 1
If target.Address <> Selection.Address Then cntWeird = cntWeird + 1
If cntWeird = 1 Then MsgBox "If both tests are equivalent this msgbox will never happen"
If cntWeird = 2 Then Exit Sub

rberke

P.S.  I searched the internet and didn't come up with much that was useful.  For instance http://www.vbaexpress.com/forum/showthread.php?p=132469  discussed similar problems, and never reached a conclusion.

New comment #1. I manually drag cell a1 and copy it onto a1:a15.  then I do an undo.  The Target is a1:a15 which is weird, but the Selection is a1 which is what I would expect.  In all my past experience with SelectionChange, I coded the modules to reference Target.  I now wonder whether I should have referenced Selection instead?

New comment #2. sheet1.Cells.PasteSpecial xlPasteValuesAndNumberFormats
 causes selectionchange to get target = $1:$65536 and selection =$A$1:$CP$21.  I guess  that makes sense.  The "target" is sheet1.cells which has the ENTIRE workbook.  Maybe in a few more days I will understand this better.
Start Free Trial