Temporary storage, Addin events

[Phil Bewig]

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

 

[Charles Williams]

Hi Phil,

Some options:
use the worksheet(s) in the XLA itself:
Thisworkbook.worksheets("TempSheet").Range("z33")
or hidden names in the active workbook
or more permanent stuff in the registry

Events:
You may need to use application level events.
Chip Pearson has a good page on this to get you started:
http://www.cpearson.com/excel/AppEvent.htm


--
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Peter-T]

Hi Phil,

Following concerns this part of your post:

For instance, if I save the fill colors of all the cells
in a worksheet, the[n] 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.

I've spent a long time wrestling with this problem and yet
to devise foolproof solution.

With user delete/insert of rows /columns and as you say,
replacement of formats could be out of sync. In xl97 &
xl2k there's no event to trap this (AFAIK). A partial
solution is to track one or more cells, in particular the
last cell. If this has moved, or "nothing" then you know
you have a problem. But what if the user inserts one row
but deletes another elsewhere. The tracked cell(s) revert
to original location.

It helps to track more cells, eg each of the "crosshair"
cells of the usedrange. But without tracking every single
cell (not viable with a large used range) it's still not
reliable. Eg, "Insert copied cells" or entire removal of
tracked cells.

If anyone else has ideas I would also be very interested!

Regards,
Peter

 

[Chip Pearson]

Phil,

For the temporary storage issue, you can use a worksheet in the
add-in itself. Even though you won't see the worksheet cells you
are using to store the values, you can save values in those
cells. You can also use the system registry to save values. See
GetSetting and SaveSetting for details.

For the events question, you need to use application level events
to get event triggers from all open workbooks. See
www.cpearson.com/excel/appevent.htm for details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Phil Bewig]

For the temporary storage issue, you can use a worksheet in the
add-in itself. Even though you won't see the worksheet cells you
are using to store the values, you can save values in those
cells. You can also use the system registry to save values. See
GetSetting and SaveSetting for details.

This won't work. I anticipate that users might change the structure
of the spreadsheet by adding or deleting cells. When I restore, I
need to know how the saved cells map back to the original cells.

What I really want is some "hook" in the object model that allows
me to store data in the cell itself, or in the worksheet itself.
I suppose I can hijack some property of cells and put my own data
there, but whatever I do will doubtless interfere with something
that someone is doing somewhere.

Phil