CODEWITHSUNDEEP
excelvba
Dead_Jester
Dead_Jester

Reputation: 602

Microsoft Excel detect user clearing cell

I have a macro that checks a cell once it has been changed and formats the input of that cell to an accepted format. This was done to make it easier for the user by allowing them to be able to input the data in multiple ways and have the cell come out correctly.

I have:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set rCells = Range("C3:C4, G9:G24, C60:C61, G66:G81")
    Application.EnableEvents = False
    If Not Application.Intersect(rCells, Range(Target.Address)) Is Nothing Then
            Format(Range(Target.Address))
    End If
    Application.EnableEvents = True
End Sub

...to catch the change and then format the input to the correct format.

I want the user however to be able to leave the cell blank.

What can I add to this block to allow the user to clear a cell using DEL or BACKSPACE without triggering format()?

I have tried:

If IsEmpty(Target) Then Exit Sub

But that doesn't seem to do it.

Thank you for your help!

Upvotes: 1

Views: 655

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can use this line to check whether all cell in Target range are empty:

If WorksheetFunction.CountBlank(Target) = Target.Cells.Count Then Exit Sub

also as I mentioned in comments

  • change Format(Range(Target.Address)) to Format(Target)
  • don't forget to change function name Format in real code to something like My_Format since there is built-in function with this name:)

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Test for =""

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "" Then Exit Sub
    Set rCells = Range("C3:C4, G9:G24, C60:C61, G66:G81")
    Application.EnableEvents = False
    If Not Intersect(rCells, Target) Is Nothing Then
            Format (Target)
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 0

Related Questions