excelvbainputcellis-empty

Allow User Input in Empty Cells, Prevent Editing or Deletion in Occupied Cells


I am currently utilizing Excel VBA to enable the following in my entire sheet:

  1. If cell is empty, allow data input.
  2. If cell not empty, do not allow data input

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

Solution

  • 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