How to trigger code when cell value is cleared?

1
votes

I'm running a Worksheet_Change sub that checks if the changed cell is in a specific column and then autofits that column.

Public Const startCol = 7 '(declared in a separate module)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = startCol Then
        Target.Columns.AutoFit
    End If
End Sub

My problem is, this macro is only run if something is put in the cell or the cell's value changes, not if the cell's value is cleared or the cell is deleted entirely.

3
excelvba
Please note that the Event is triggered even if a cell is cleared (even if that cell is already empty) The event will also be triggered if an empty row or column is deleted as well. – Gary's Student

3 Answers

1
votes

The Target is a range, you can then check if the range's value is equal to "" empy string ->

If Target.Value2 = "" Then MsgBox ("Emptied cell " & Target.Address)

1
votes

Here is a kind-of solution, if you are wanting worksheet changes in column 7 AND deletion of contents in column 7 (via the delete key) then this kinda might be reasonable:

In ThisWorkbook section

Private Sub Workbook_Open()
    Application.OnKey "{DELETE}", "ColFit"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "{DELETE}"
End Sub

In a module

Sub ColFit()
Select Case TypeName(Selection)
    Case "Range"
        Selection.ClearContents
        Worksheets("Sheet1").Columns("G:G").AutoFit
    Case "ChartArea"
        ActiveChart.Parent.Delete
    Case "PlotArea"
        ActiveChart.Parent.Delete
End Select
End Sub

On the Worksheet

Private Sub Worksheet_selectionChange(ByVal Target As Range)
   Worksheets("Sheet1").Columns(startCol).AutoFit
End Sub

save and reopen, now some more bases are covered ;)

also consider(in ThisWorkbook):

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Application.OnKey "{DELETE}", "ColFit"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.OnKey "{DELETE}"
End Sub

which lets you work with multiple books without issues

1
votes

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = startCol Then
        Columns(startCol).AutoFit
    End If
End Sub