Worksheet Change - Revert Back To Original Cell Value (as part of error checking for instance)

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code as part of a worksheet change event.

Code:
        Case Is = 10
            'execute procedure4
            Stop
            MsgBox Target.Value
            If Target.Value = "- - - - -" Then
                Exit Sub
            End If
            Stop
            MsgBox "Column J: Signature"

2 Questions:

1) If target value = "- - - - -", it's basically a null value and is an invalid selection from the validation list for the changed cell. How can I return the value back to the original value before "- - - - -" was selected?
2) If the user selects the value (from the validation list available to the user) that is the same that is already there, will the worksheet change event be triggered? If so, how can that be avoided?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I think I've found a solution.
 
Last edited:
Upvote 0
Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers.
 
Upvote 0
It was simply a concept I applied.
I plugged the original value into a temporary holding cell, referencing it in the comparison. I just applied the value of the temp. holding cell back to the selection.
 
Upvote 0
Right, your method will work fine.

Alternatively, you can still keep it in the event procedure by using the following methods.

* Assuming the Case in your original sample code is checking the target cell, and using this assumption instead of checking the target address in my code below:

For #1: The following will revert the cell value if the invalid option is selected. The EnableEvents method makes sure to not triggering the Change event.
VBA Code:
'...
Case Is = 10
        If Target.Value = "- - - - -" Then
            ' Invalid option is selected
            ' Use MsgBox if you want to warn the user
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
        Else
            ' Valid option is selected
            ' Your code, if any
        End If
'...

For #2: Yes, the Change event will be triggered again. You can't avoid it (* see the note below) but you can store the previous selection in a module variable, and check the new value when the event is triggered, then exit the event procedure if the oldValue and newValue are the same. In fact, you can also use the same concept to switch back to the old value on the invalid selection (#1 above), but I prefer the Undo method.

* Note: The Change is an event procedure, and if you set Application.EnableEvents = False then you can avoid event procedure triggering, however, this is not something your want.
 
Upvote 0
Thank you Suat!!
Some great learning there for me in your answer!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top