Data entry cell must contain a zero

[leopardhawk]

Hello forum friends, my worksheet has one particular cell where the user will be entering a number. Is it possible that if the user clears the cell, deletes whatever number is in it, that the cell will ALWAYS contain at a minimum, a zero (0)...?? I have another cell on the worksheet that is doing a COUNT and this 'blank' cell is giving me an error...

Thanks!

 

[Joe4]

Right-click on the sheet tab name at the bottom of the page, select "View Code", and paste this code in the VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
   
    If Target.Address(0, 0) = "B4" Then
        If Len(Target) = 0 Then
            Application.EnableEvents = False
            Target = 0
            Application.EnableEvents = True
        End If
    End If

End Sub

Just change the "B4" to whatever cell you want to apply this to, and it will run automatically when that cell is changed.

 

[Joe4]

Here is a better version that will handle the case if they clear multiple cells at one time (the first code only works if they are clearing exactly that one cell):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim rng As Range
    
'   Set cell that you want to watch
    Set cell = Range("B4")
    
    Set rng = Intersect(Target, cell)
    
    If Not rng Is Nothing Then
        If Len(cell) = 0 Then
            Application.EnableEvents = False
            cell = 0
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[leopardhawk]

@Joe4 Thank you. Is it possible to do this without using VBA? If I add the code, I think I have to save the file as macro-enabled and the users will also then have to enable macros when they open the workbook? I am trying to make this as simple as possible for the majority of the target audience who may not be comfortable enabling anything. Thanks!

 

[Joe4]

No, formulas can only update the cells that they are located in, and you can only have either a hard-coded value or a formula in a cell, never both at the same time.
So you cannot do this without VBA.

What you can do is have them update some other cell, like let's say A4.
Then have this formula in cell B4:
=A4
and use B4 instead of A4 in your count.

If A4 is empty, B4 will return 0. You can even lock B4 so they cannot touch that formula.

 

[leopardhawk]

@Joe4 Thank you! I think that will work just fine for what I am trying to do. I can also 'hide' the results in B4 with ;;; in a custom format. Thanks again!

 

[Joe4]

Thank you! I think that will work just fine for what I am trying to do. I can also 'hide' the results in B4 with ;;; in a custom format. Thanks again!

You are welcome.