SheetSelectionChange Target size Limitation?

[Guillaume E.]

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$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$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$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$12,$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$11:$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$12,$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.

 

[Tom Ogilvy]

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.