VBA Macro - prompt confirmation message before editing - restore initial formula if no

In your Selection Change Event handler, you pick up the current value. You need to change that to get the formula instead.

You're welcome.

Thread???





If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Personally I would use the change event. Have it store the new value in a variable, disable events, then have it perform an undo which reverts to old value, then prompt the user and set the value of the cell to the desired value by either leaving it as the old or replacing the old with the stored new value and lastly enabling events again.

In the above a couple things to consider. Check that the change is on a single cell and that potentially if its within the range you want to check for. You may also consider checking if it was originally blank, just skip the event. As a matter of preference you can eval the opposite (had something and new entry is blank (either let it be blank and skip or still prompt).

This method can greatly reduce the number of times you trigger an event/code.