Bug in Excel 2000? (CurrentRegion property triggers events)
 

Greetings,
In order to get a better understanding of the CurrentRegion property
I wrote the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.CurrentRegion.Address
End Sub

Now if A1 (say) is part of a block of non-blank cells and I select A1
then *three* times I end up having to dismiss the message box.
Apparantly, the CurrentRegion property, when it returns more than 1
cell, triggers two selection change events. At least it doesn't hang up
in an infinite loop.

To test this hypothesis I wrote

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Sub test()
Dim R As Range
Set R = Range("A1").CurrentRegion
End Sub

The act of running test() triggers two selection changes. In my
experiment, A1:B12 is a range of contiguous non-blank cells. I start by
selecting (say) D10 and then run test(). Twice a message box with
$A$1:$B$12 appears, after which D10 is still selected. Wierd. I would
find the bug more understandable if the second message box was $D$10.
Application.EnableEvents provides a work-around.

Has anyone else encountered this bug? Are there any other phantom
events I should be aware of?

-John Coleman

Bug in Excel 2000? (CurrentRegion property triggers events)
 

Didn't generate any events in excel 2003. I don't have xl2000 handy to test
with, but will test it later.

--
Regards,
Tom Ogilvy

"John Coleman" wrote in message
ups.com...
Greetings,
In order to get a better understanding of the CurrentRegion property
I wrote the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.CurrentRegion.Address
End Sub

Now if A1 (say) is part of a block of non-blank cells and I select A1
then *three* times I end up having to dismiss the message box.
Apparantly, the CurrentRegion property, when it returns more than 1
cell, triggers two selection change events. At least it doesn't hang up
in an infinite loop.

To test this hypothesis I wrote

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Sub test()
Dim R As Range
Set R = Range("A1").CurrentRegion
End Sub

The act of running test() triggers two selection changes. In my
experiment, A1:B12 is a range of contiguous non-blank cells. I start by
selecting (say) D10 and then run test(). Twice a message box with
$A$1:$B$12 appears, after which D10 is still selected. Wierd. I would
find the bug more understandable if the second message box was $D$10.
Application.EnableEvents provides a work-around.

Has anyone else encountered this bug? Are there any other phantom
events I should be aware of?

-John Coleman

Bug in Excel 2000? (CurrentRegion property triggers events)
 

Tom,

Just tested this in...

xl2002 - no events
xl 2000 - repeats 3 times
xl 97 - repeats 3 times

Very strange.
Regards,
Jim Cone
San Francisco, USA


"Tom Ogilvy" wrote in message ...
Didn't generate any events in excel 2003.
I don't have xl2000 handy to test with, but will test it later.--
Regards,
Tom Ogilvy



"John Coleman" wrote in message
ups.com...
Greetings,
In order to get a better understanding of the CurrentRegion property
I wrote the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.CurrentRegion.Address
End Sub

Now if A1 (say) is part of a block of non-blank cells and I select A1
then *three* times I end up having to dismiss the message box.
Apparantly, the CurrentRegion property, when it returns more than 1
cell, triggers two selection change events. At least it doesn't hang up
in an infinite loop.

To test this hypothesis I wrote

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Sub test()
Dim R As Range
Set R = Range("A1").CurrentRegion
End Sub

The act of running test() triggers two selection changes. In my
experiment, A1:B12 is a range of contiguous non-blank cells. I start by
selecting (say) D10 and then run test(). Twice a message box with
$A$1:$B$12 appears, after which D10 is still selected. Wierd. I would
find the bug more understandable if the second message box was $D$10.
Application.EnableEvents provides a work-around.

Has anyone else encountered this bug? Are there any other phantom
events I should be aware of?

-John Coleman

Bug in Excel 2000? (CurrentRegion property triggers events)
 

Hi,

I tested the following macro in Excel 97 and Excel 2000, and got the
similar result. (perhaps these properties use the same function
(SELECT.SPECIAL in excel4) internally)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub

Sub test()
Dim R As Range
Range("A1").Select
Range("B1") = 1
Range("B2") = "=B1"
Range("A2:A3").FormulaArray = "=1"
Set R = Range("A1").CurrentRegion '2 times
Set R = Range("A2").CurrentArray '2 times
Set R = Range("A1").SpecialCells(xlCellTypeFormulas) '1 time
Set R = Range("A1").SpecialCells(xlCellTypeConstants) '1 time
Set R = Range("A1").SpecialCells(xlCellTypeBlanks) '1 time
Rows(2).Hidden = True
Set R = Range("A1").SpecialCells(xlCellTypeVisible) '1 time
Rows(2).Hidden = False
End Sub

BTW, clicking row and column headings (1, 2, 3... , A, B, C...) also fire
the selection change event.

--
HTH,

okaizawa