mikerickson
02-18-2008, 12:37 AM
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?
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?