B
Bill P
Hi all,
I have a situation with Application.Undo that is presenting some
problems. I monitor both the Worksheet_SelectionChange and
Worksheet_Change events, and do something in either case. If the
selection changes, I change some formatting, and if the worksheet
changes, I validate some data. Normally, the Worksheet_Change event is
called before the Worksheet_SelectionChange event.
In the Worksheet_Change function, I want to record the previous state
of the worksheet, as I want to be able to revert back to that state if
the user runs my undo macro. I record the state of the sheet every
time the user makes a change, and it's worked well so far. I use
Application.Undo to get back to the old state, and then
Application.Undo again to change back to the updated version. When the
user types in a value, it works well, since the Worksheet_Change is the
first event fired. However, when the user auto fills, the
Worksheet_SelectionChange is fired twice, in the following sequence:
SelectionChange -> Change -> SelectionChange
Therefore, the code in the SelectionChange clears the undo buffer, so
Application.Undo fails in the case when the user auto fills.
I'm looking for a nice way to deal with this. Ideally, I could just
escape out of the SelectionChange event function the first time if I
could tell that a Change event was down the pipeline (which I'm pretty
sure is not possible). I can't really think of a way to fix this
problem, and am looking for ideas for a workaround which would provide
the intended functionality.
I have a situation with Application.Undo that is presenting some
problems. I monitor both the Worksheet_SelectionChange and
Worksheet_Change events, and do something in either case. If the
selection changes, I change some formatting, and if the worksheet
changes, I validate some data. Normally, the Worksheet_Change event is
called before the Worksheet_SelectionChange event.
In the Worksheet_Change function, I want to record the previous state
of the worksheet, as I want to be able to revert back to that state if
the user runs my undo macro. I record the state of the sheet every
time the user makes a change, and it's worked well so far. I use
Application.Undo to get back to the old state, and then
Application.Undo again to change back to the updated version. When the
user types in a value, it works well, since the Worksheet_Change is the
first event fired. However, when the user auto fills, the
Worksheet_SelectionChange is fired twice, in the following sequence:
SelectionChange -> Change -> SelectionChange
Therefore, the code in the SelectionChange clears the undo buffer, so
Application.Undo fails in the case when the user auto fills.
I'm looking for a nice way to deal with this. Ideally, I could just
escape out of the SelectionChange event function the first time if I
could tell that a Change event was down the pipeline (which I'm pretty
sure is not possible). I can't really think of a way to fix this
problem, and am looking for ideas for a workaround which would provide
the intended functionality.