SheetSelectionChange Target size Limitation?
 

I have been using the SheetSelectionChange event in a Excel ComAddin
application as a workaround for the buggy RefEdit Control.

This ComAdding allows user to select multiple cells area within one
spreadsheet. I have noticed that the SheetSelectionChange
Target.Address fails to return new areas once I have selected more
than 19 distinct areas.

For example, if I implement the code below in my Com Addin, then
select consecutively 22 distinct areas of 2 cells: A$11:$A$12, then
add B$11:$B$12, then add C$11:$C$12,
the Target.Address fails to return the added area after 19 consecutive
selections.

See sample code below:

' Transmit all XL event down to the ReportManager
Private Sub oxlApp_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
Debug.Print "SheetSelectionChange = " + Target.Address
End Sub

See debug.print response below (# areas comment was added for
readibility):
2 areas: SheetSelectionChange = $A$11:$A$12,$B$11:$B$12
3 areas: SheetSelectionChange = $A$11:$A$12,$B$11:$B$12,$C$11:$C$12
.....
19 areas: SheetSelectionChange =
$A$11:$A$12,$B$11:$B$12,$C$11:$C$12,$D$11:$D$12,$E $11:$E$12,$F$11:$F$12,$G$11:$G$12,$H$11:$H$12,$I$1 1:$I$12,$J$11:$J$12,$K$11:$K$12,$L$11:$L$12,$M$11: $M$12,$N$11:$N$12,$O$11:$O$12,$P$11:$P$12,$Q$11:$Q $12,$R$11:$R$12,$S$11:$S$12,$T$11:$T$12
20 areas: SheetSelectionChange =
$A$11:$A$12,$B$11:$B$12,$C$11:$C$12,$D$11:$D$12,$E $11:$E$12,$F$11:$F$12,$G$11:$G$12,$H$11:$H$12,$I$1 1:$I$12,$J$11:$J$12,$K$11:$K$12,$L$11:$L$12,$M$11: $M$12,$N$11:$N$12,$O$11:$O$12,$P$11:$P$12,$Q$11:$Q $12,$R$11:$R$12,$S$11:$S$12,$T$11:$T$12,$U$11:$U$1 2
21 areas: SheetSelectionChange =
$A$11:$A$12,$B$11:$B$12,$C$11:$C$12,$D$11:$D$12,$E $11:$E$12,$F$11:$F$12,$G$11:$G$12,$H$11:$H$12,$I$1 1:$I$12,$J$11:$J$12,$K$11:$K$12,$L$11:$L$12,$M$11: $M$12,$N$11:$N$12,$O$11:$O$12,$P$11:$P$12,$Q$11:$Q $12,$R$11:$R$12,$S$11:$S$12,$T$11:$T$12,$U$11:$U$1 2

Please let me know if I am hitting a buffer size limitation for the
Target variable. If this is the case, can you advise on potential
work-around?

Also, are you aware of a more reliabe RefEdit Control that would work
in a Com Addin environment,

Thank you all for your help.

SheetSelectionChange Target size Limitation?
 

More than likely, the target object is holding the reference with no
problem. The address property is running into a string length limitation
(255 characters - your 19 selections is 239 characters) more than likely -
this is an output problem - not a real problem.

Try selecting 25 individual non-adjacent cells in column 1 or

Target.Address(0,0)

and you should be able to squeeze out some more, but regardless, Target
still holds a reference to all the selected cells.

--
Regards,
Tom Ogilvy


Guillaume E. wrote in message
om...
I have been using the SheetSelectionChange event in a Excel ComAddin
application as a workaround for the buggy RefEdit Control.

This ComAdding allows user to select multiple cells area within one
spreadsheet. I have noticed that the SheetSelectionChange
Target.Address fails to return new areas once I have selected more
than 19 distinct areas.

For example, if I implement the code below in my Com Addin, then
select consecutively 22 distinct areas of 2 cells: A$11:$A$12, then
add B$11:$B$12, then add C$11:$C$12,
the Target.Address fails to return the added area after 19 consecutive
selections.

See sample code below:

' Transmit all XL event down to the ReportManager
Private Sub oxlApp_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
Debug.Print "SheetSelectionChange = " + Target.Address
End Sub

See debug.print response below (# areas comment was added for
readibility):
2 areas: SheetSelectionChange = $A$11:$A$12,$B$11:$B$12
3 areas: SheetSelectionChange = $A$11:$A$12,$B$11:$B$12,$C$11:$C$12
....
19 areas: SheetSelectionChange =

$A$11:$A$12,$B$11:$B$12,$C$11:$C$12,$D$11:$D$12,$E $11:$E$12,$F$11:$F$12,$G$1
1:$G$12,$H$11:$H$12,$I$11:$I$12,$J$11:$J$12,$K$11: $K$12,$L$11:$L$12,$M$11:$M
$12,$N$11:$N$12,$O$11:$O$12,$P$11:$P$12,$Q$11:$Q$1 2,$R$11:$R$12,$S$11:$S$12,
$T$11:$T$12
20 areas: SheetSelectionChange =

$A$11:$A$12,$B$11:$B$12,$C$11:$C$12,$D$11:$D$12,$E $11:$E$12,$F$11:$F$12,$G$1
1:$G$12,$H$11:$H$12,$I$11:$I$12,$J$11:$J$12,$K$11: $K$12,$L$11:$L$12,$M$11:$M
$12,$N$11:$N$12,$O$11:$O$12,$P$11:$P$12,$Q$11:$Q$1 2,$R$11:$R$12,$S$11:$S$12,
$T$11:$T$12,$U$11:$U$12
21 areas: SheetSelectionChange =

$A$11:$A$12,$B$11:$B$12,$C$11:$C$12,$D$11:$D$12,$E $11:$E$12,$F$11:$F$12,$G$1
1:$G$12,$H$11:$H$12,$I$11:$I$12,$J$11:$J$12,$K$11: $K$12,$L$11:$L$12,$M$11:$M
$12,$N$11:$N$12,$O$11:$O$12,$P$11:$P$12,$Q$11:$Q$1 2,$R$11:$R$12,$S$11:$S$12,
$T$11:$T$12,$U$11:$U$12

Please let me know if I am hitting a buffer size limitation for the
Target variable. If this is the case, can you advise on potential
work-around?

Also, are you aware of a more reliabe RefEdit Control that would work
in a Com Addin environment,

Thank you all for your help.