providing a sheet-copy event or copy CustomProperties

[Guest]

I have an add-in which stores sheet-based metadata in the workbook. It does
this by putting the metadata in CustomProperties found in the Worksheet
object. When the sheet is moved to another workbook, my data goes with it.
However, when the sheet is copied, the CustomProperties are not copied. The
easiest fix to my problem is for excel to simply to copy this data when the
user copies the sheet. This is probably how it should work anyway.

I would be happy to do this myself by catching some event. However, there
is no Copy sheet event. The closest thing is Workbook_NewSheet event but
this doesn't discriminate between copies and new worksheets. A second way
you could fix my issue is to provide a new event or generalize the NewSheet
event. This would be more invasive to your UI, but it's reasonable that an
excel programmer would want this event information.

Either of these fixes would make me happy. Thanks.

 

[Guest]

I just noticed that the NewSheet event doesn't actually get fired for sheet
copies. Only SheetActivate and SheetDeactivate are fired. This is an even
more limited situation than I depicted. My suggestion is still relevant, but
the potential to mess up existing applications is greater. Probably the best
thing to do would be to create a new Event handling sheet copy, move, and
rename as one event. But like I said the first fix is the easiest.

Thanks, Carlos

 

[Stephen Bullen]

Hi Carlos,

I have an add-in which stores sheet-based metadata in the workbook. It does
this by putting the metadata in CustomProperties found in the Worksheet
object. When the sheet is moved to another workbook, my data goes with it.
However, when the sheet is copied, the CustomProperties are not copied. The
easiest fix to my problem is for excel to simply to copy this data when the
user copies the sheet. This is probably how it should work anyway.

This is a peer-to-peer newsgroup, where the questions are answered by other
Excel users, not Microsoft staff. So it's unlikely that your suggestion will be
heard at Microsoft.

That said, one common way to store metadata in a worksheet is to create
sheet-level, hidden, defined names, using Worksheet.Names.Add

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk