SpecialCells causes SelectionChange

[mikerickson]

I responded to a question in this thread, http://www.vbaexpress.com/forum/showthread.php?t=17797 with a SelectionChange routine that used SpecialCells.

I discovered that SpecialCells triggers a SelectionChange event. And am very confused.
With this test routine,Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static i As Long
If Not (Application.Intersect(Target, Range("1:6")) Is Nothing) Then
If Comments.Count = 0 Then
Range("b7").Select
Else
i = i + 1
If Application.Intersect(Target, Cells.SpecialCells(xlCellTypeFormulas)) Is Nothing Then
MsgBox "x": i = 0: Rem reset C
Range("b7").Select
End If
MsgBox Target.Address & ":" & i
End If
Else
'i = 0: Rem reset B
End If
'i = 0: Rem reset A
End SubIf I click on a cell in 1:6, the cursor dances a bit and a message box appears showing the address of my formula followed by ":28".
Then 27 identical message boxes appear.
Then "x".
Then a message box with the cell address I click on and ":0" if my test cell is a non-formula, ":28" if it has a formula.

Putting the reset in different places has some effect, but the intial msgbox is always 28.

Adding formulas to the sheet doesn't change the 28.

Any insights?

[Bob Phillips]

Just goes into a loop for me. What am I doing wrong?

[mikerickson]

Are there formulas in your sheet? (I would think that would give a Not Found error)
A commented cell is needed.

This is annoying, its not behaving as it used to, I can't reproduce my own error.

If I don't disable events in the code in the other thread, I get cascade problems.

[Adamski]

I know this is an old thread, but I have also just discovered that SpecialCells triggers a SelectionChange event. I was using SpecialCells on a Range that was not on the active sheet. That non-activesheet still fired it's SelectionChange event even with ScreenUpdating off.

Did you find out if it was something you were doing to cause it, or is it just an Excel bug? Is there an alternative to get the xlCellTypeFormulas of a range?

Cheers

[mdmackillop]

ScreenUpdate will not make a difference, you'll need to set EnableEvents to False.

[Adamski]

Yep, thanks.

I have surrounded my SpecialCells code with EnableEvents off, on. I only mentioned the ScreenUpdate to emphisize I was not changing sheet or range.