Run macro if cell is left blank

[EssKayKay]

Hello,

I’m working on small issue where if a cell value is edited and inadvertently left blank, I want to run a macro that returns the previous value that was edited/deleted when the user leaves the “empty” cell (presumably by Enter or Arrow). I only want This to apply to cells in range(“M33:M2033”). Any suggestions would be appreciated.

Thanks for viewing,
Steve

 

[Scott Huish]

This will only work if only 1 cell was changed. Not if you copy and paste a blank cell into a whole bunch of cells. Does this work for you?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range
Set d = Intersect(Target, Range("M33:M2033"))
If d Is Nothing Then Exit Sub
If d.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If d = "" Then Application.Undo
Application.EnableEvents = True
End Sub

 

[EssKayKay]

This will only work if only 1 cell was changed. Not if you copy and paste a blank cell into a whole bunch of cells. Does this work for you?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range
Set d = Intersect(Target, Range("M33:M2033"))
If d Is Nothing Then Exit Sub
If d.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If d = "" Then Application.Undo
Application.EnableEvents = True
End Sub

Thank you Scott for your quick response. This appears to be doing exactly as I desired. I'll have to do more testing which may be a bit of an issue as my primary laptop just got sick (the one I'm using/borrowing is less than desirable). I'll keep you posted but this really looks good.

Again, much appreciated,
Steve K.