totesbotes
New Member
- Joined
- Jan 29, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
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
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
VBA Code:
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
VBA Code: