I am currently utilizing Excel VBA to enable the following in my entire sheet:
The code that I have provided below is able to perform the second requirement by resetting the content back to the original content whenever the user wishes to make changes to an occupied cell. However, when I try to input data for an empty cell, it simply clears the cell, treating it as if the cell is occupied. I am not sure what is wrong with my code here. Hope to seek any advice. Thank you
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Value = "" Then
' Cell is empty, allow data input
Else
' Cell is not empty, prevent any changes (deletion/input)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End Sub
This behaviour is correct because when the event is invoked the cell already contains data (not empty). To get the desired result add the SelectionChange event to your code, and modify the Change event like this
Dim buffer As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
buffer = Target
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If buffer = "" Then
' Cell is empty, allow data input
Else
' Cell is not empty, prevent any changes (deletion/input)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If
End Sub