Default certain cells to 0 if blank

[kiwikiki718]

Hello is there a way to default certain fields to displsy 0 if cell is blank?

Ex. I have cells A1,A13,A23 that I want the defaulted value to be 0 if it is blank. Users can update the field but if they delete the data within the cells it would still default to 0.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Delete the data in one of the cells.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1,A13,A23")) Is Nothing Then Exit Sub
    If Target = "" Then Target = 0
    Application.ScreenUpdating = False
End Sub

 

[kiwikiki718]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Delete the data in one of the cells.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1,A13,A23")) Is Nothing Then Exit Sub
    If Target = "" Then Target = 0
    Application.ScreenUpdating = False
End Sub

Thank you this worked!

 

[mumps]

You are very welcome.

 

[effingeh]

hey mumps! this worked perfectly when i used the code for a slightly larger range and when a single cell was deleted, however, if more than 1 cell was selected and deleted, it wouldnt default back to '0'. is there an update that could be added to the code to make sure multiple cell deletion is covered? thanks!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Intersect(Target, Range("A1,A13,A23")) Is Nothing Then Exit Sub
    Dim rng As Range
    For Each rng In Target
        If rng = "" Then rng = 0
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[effingeh]

it worked! i really appreciate the help and quick update!

 

[mumps]

Glad it worked out.