I know that as soon as I do something to Excel in VBA I
loose my undo buffer which is extremely annoying. I am
trying to get round this by building me own undo stack. I
can undo my code and I can store other changes from the
applications App_SheetChange event.

The problem I have is format changes do not fire the
sheet change event. Is there any way to detect when this
occurs?

Alternatively any other ideas on undo solutions would
also be much appreciated.

Thanks

Nigel

Hi Nigel,

AFAIK there's know of any simple way to do what you seek.
As you say Format changes do not trigger an event, though
you may be able to track changes that occur following
SelectionChange and/or Change events, compared with the
state prior. Along the lines of what you are already doing
but could involve a lot more code. But I don't think it
will ever be foolproof.

Depending on your needs you may be able to copy and paste
(or pastespecial formats) to a hidden sheet somewhere. I
find this method works well for my needs, I don't offer a
multi level undo but undo to original state by pasting
back. It does have limitations as a catch all. One, of
several problems to overcome, is if user inserts/deletes
rows or columns (also no event).

You might be able to tie this into the "Undo Method" (see
help), although I just attach to a Cancel button.

Regards,
Peter

-----Original Message-----

I know that as soon as I do something to Excel in VBA I
loose my undo buffer which is extremely annoying. I am
trying to get round this by building me own undo stack. I
can undo my code and I can store other changes from the
applications App_SheetChange event.

The problem I have is format changes do not fire the
sheet change event. Is there any way to detect when this
occurs?

Alternatively any other ideas on undo solutions would
also be much appreciated.

Thanks

Nigel
.

Hi Nigel

Look on John's site for a example
http://www.j-walk.com/ss/excel/tips/tip23.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Nigel" wrote in message ...

I know that as soon as I do something to Excel in VBA I
loose my undo buffer which is extremely annoying. I am
trying to get round this by building me own undo stack. I
can undo my code and I can store other changes from the
applications App_SheetChange event.

The problem I have is format changes do not fire the
sheet change event. Is there any way to detect when this
occurs?

Alternatively any other ideas on undo solutions would
also be much appreciated.

Thanks

Nigel

Thanks Peter as I thought it will be al hell of lot of
work to do and I'm not sure I would be happy running that
much code every time a user made a selection change. I
looks like I have no choice other than to only offer one
level undo.
I don't understand why Excel clears the undo buffer, why
you can't just add your own procedure into the stack.
When your trying to produce a quality Addin the end user
wants everything else to behave as normal eg. Multi undo.

Cheers anyway.

Nigel