what is the vba code to return a cell to its original value
- User
what is the vba code to return a cell to its original value
- Assistant
To return a cell to its original value using VBA code, you need to store the original value in a separate variable before making any changes. Later, you can assign this original value back to the cell. Here's an example of VBA code that does this:
Sub RestoreCellValue() Dim originalValue As Variant Dim targetCell As Range ' Set the target cell where you want to restore the value Set targetCell = Range("A1") ' Store the original value of the target cell in a variable originalValue = targetCell.Value ' Modify the target cell value (just for demonstration purposes) targetCell.Value = "New Value" ' Restore the original value to the target cell targetCell.Value = originalValue End SubIn this example, the code sets the targetCell variable to the cell A1. It then stores the original value of that cell in the originalValue variable. The code modifies the targetCell value to "New Value" for demonstration purposes. Finally, it assigns the originalValue back to the targetCell to return it to its original value.
- User
How can I stop this code from looping: If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("H6:H506")) Is Nothing Then If Target.Offset(0, -6).Value <> "Service" Or Target.Offset(0, -6).Value <> "Serviced" Then MsgBox "Only 'Service Due Date' should be recorded here." Target.Value = "" End If End If
- Assistant
To stop the code from looping, you can add an additional condition using the
Application.EnableEventsproperty. By setting it toFalsebefore changing the target cell value and then setting it back toTrue, you can temporarily disable the event triggers, preventing the code from triggering itself recursively. Here's the modified code:If Target.CountLarge > 1 Then Exit Sub If Not Intersect(Target, Range("H6:H506")) Is Nothing Then If Target.Offset(0, -6).Value <> "Service" Or Target.Offset(0, -6).Value <> "Serviced" Then MsgBox "Only 'Service Due Date' should be recorded here." Application.EnableEvents = False ' Disable event triggers temporarily Target.Value = "" Application.EnableEvents = True ' Enable event triggers again End If End IfWith this modification, the event triggers will be temporarily disabled while changing the target cell value and then re-enabled once the changes are made, avoiding looping of the code.