Check if cell was empty Excel VBA

Question

I am writing an Excel Macro to display a warning if the user edits a cell that was previously empty. Basically if a cell is edited, is in column 1, and already contains text I want to display a warning But if it does not have text already, I do not want to.

So what I have tried is the following

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Not IsEmpty(Cells(Target.Row,Target.Column)) Then
        Application.EnableEvents = False
        MsgBox "Some Message"
        Application.EnableEvents = True
    End If
End Sub

The issue I am having is when I am getting the cell to see if it was empty or not, it will never return that it was because, well the user just added stuff to it.

So I want to know if there is a simple way to check the previous state of that cell. I want to find out if the cell WAS empty. Is this possible?

http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba — mr.Reband, Sep 12 '13 at 14:21
@mr.Reband Thanks for that, I tried searching for a question on here but I guess I was not using the proper search terms. — MZimmerman6, Sep 12 '13 at 14:49

score 4 · Accepted Answer · answered Sep 12 '13 at 14:18

4

You could use the Worksheet_SelectionChange event to capture the value of the cell that the user has selected in a variable, which happens before they make any changes.

Then when Worksheet_Change is fired, you can refer to that variable.

answered Sep 12 '13 at 14:18

Declan_K

6,726
2
19
30

This is exactly what I ended up doing, and was based off of the comment that mr.Reband posted as a comment. – MZimmerman6 Sep 12 '13 at 14:53

Check if cell was empty Excel VBA

1 Answers1