Error Message when changing value of particular cell and restore previous value VBA

Question

I am trying to create a code using vba whereby I want to make a certain cell B2 read-only and unchangeable when session is unlocked (I have a lock and unlock module for later whereby locked disallow editing and unlocked allows editing).

The code aims to give an error message and restore the cell to the previous value before edits are made when session is locked.

Problem: However, currently the results from this code successfully gave an error message when edit is done when locked BUT made the cell blank after the error message when I actually want it to restore to the previous value before edit was made

Please help if you know what went wrong and thank you in advance

Public locked As Boolean
Dim oldValue As Variant

Private Sub worksheet_SelectionChange(ByVal Target As Range)
    oldValue = Target.Value
End Sub

Private Sub worksheet_Change(ByVal Target As Range)

If Target.Row = 2 And Target.Column = 2 Then
If locked Then
If Target.Value <> oldValue Then
    Target.Value = oldValue
    MsgBox "You are not allowed to edit!"
End If
End If
End If

End Sub

Answer 1

An Events Duo: Change and SelectionChange

• In the Worksheet_SelectionChange event you can safely use Target because you can select only one cell at a time.
• In the Worksheet_Change event you could have copied hundreds of cells which are the cells Target is referring to. But you are only interested in one cell, so use intersect and use a variable to get this single cell and do the operations necessary on it. Also, to avoid an endless loop by constantly retriggering the event, disable events before writing to the worksheet but don't forget to enable them immediately after writing.

Option Explicit

' If you're using the variables outside of this module, they need
' to be declared as 'Public'. Otherwise, use 'Private' ('Dim' is the same 
' but it's kind of reserved for inside procedures).

Public Locked As Boolean

Private Const CellAddress As String = "B2"
Private OldValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range(CellAddress), Target) Is Nothing Then
        OldValue = Target.Value
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iCell As Range: Set iCell = Intersect(Range(CellAddress), Target)
    If Not iCell Is Nothing Then
        If Locked Then
            If StrComp(CStr(iCell.Value), CStr(OldValue), vbTextCompare) _
                    <> 0 Then
                Application.EnableEvents = False ' prevent retriggering
                iCell.Value = OldValue
                Application.EnableEvents = True
                MsgBox "You are not allowed to edit!"
            End If
        End If
    End If
End Sub