I need some help writing a set of macros that will be distributed as
an addin and activated using a custom toolbar. The macros should work
in any version of Excel from XL97 to the present.

My first question concerns temporary storage. As the macros operate,
they need to store various bits of state information about the sheets
and cells where they are used. For instance, the macros exist in
essentially two states -- on and off -- with different options, where
a state is tied to a particular worksheet. When the user activates a
different worksheet, the state may change and the macros must respond,
making different toolbar buttons available. As another example, some
of the macros must change the fill color of a cell, and restore the
original color when the macro is switched off; the original color must
remain even if the user changes the structure of the spreadsheet in
the meantime.

I would like to have some temporary storage where I can save this
state information. One option is to save it in objects created by the
macros, but this means a lot of work. For instance, if I save the
fill colors of all the cells in a worksheet, they the user deletes a
row, I have to somehow modify the saved fill colors so they track back
to the proper cells, rather than being one row off below the deleted
row.

Is there some property of a cell or worksheet that a programmer can
use for temporary storage? The best idea I have is to store what I
need in the comments attached to each cell, but that fails if the
users write comments, and in any case I also need storage at the
worksheet level.

A related problem involves events. I need to trap several events
related to the user's worksheet -- the activate event so I can reset
the toolbar buttons, the change event so I can reset the fill colors
appropriately, and the before_save event so I can turn off the macros
and restore the spreadsheet if the user forgot to do it himself. But
the trigger for those events calls code in the user workbook, not the
addin. Now can I notify the addin of these events?

Thanks for any assistance.

Phil