My original logic said that I should intercept the change event for the
selected worksheet and, if new rows were inserted, I could pre-set whatever
markers and unique identifiers I wanted in the new rows. By doing this, I
could create the named ranges as the rows are added rather than having to
rely on a user to run a macro before they send us the spreadsheet.

This worked great on my PC, which runs Excel 2003. When I ran the same
macros on Excel 2000 I found that the worksheet change event wasn't being
fired when you insert rows - very irritating. Sometime around that
discovery, I decided that worksheet events were a waste of time in this
case.

The reason they won't convert is because there is just no time to do a
conversion and no experience within the department of anything other than
Excel. It really is a simple case of "the devil you know". As with so many
of these spreadsheets that I come across, they start out as a simple record
and grow massively as people find that they contain useful information. By
the time you discover that Excel isn't the most appropriate tool, it's too
late to switch to something else without a formal investment of time and
cash from the IT department.

Steve


"Peter T" <peter_t@discussions wrote in message
...
Understood. The event routine I posted can work same way with cell
comments
with only slight modification. Obviously read and, if necessary add new
comment(s) or change the text in an existing comments in lieu of the
cells.
Only other change would be instead of the MAX formula, maintain a counter
that only increments. This counter could be in a hidden shape or as a
"named" value.

In your first post you said you were looking for events to handle the
changes(subject to testing the event code I posted appears to work), or is
that effectively a macro that the client will not accept.

FWIW, a named array can store 10k elements (values), though don't think
that
would serve any additional purpose.

For my curiosity and only if non sensitive, are you able to describe why
this 50mb DB can't be converted to something more conventional (and safe)
and the "process" it is used for.

Regards,
Peter T

"Steve Barnett" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
I follow what you're doing but 10k comments!!! Surely client would
accept
a
helper column, very significantly less file size in an already big one.


The client has a tight change control system. Adding macros and a
comments
to a cell will be waved through as it's not perceived to add much to the
worksheet. Adding a column to an existing worksheet will require a full
review possibly taking anything up to six weeks to go through.

The person who would have to put this through change control has, very
honestly, admitted that he no longer has the will to even try as past
experience shows that the protection afforded to changing this
spreadsheet
means that the first attempt to get the change agreed would almost
certainly
fail.

It's frustrating, but very understandable. This spreadsheet has become
key
to one of their major processes and they're utterly paranoid about it's
contents (comments and range names are not thought of as "contents").

Killer huh?
Steve