How to keep user from accidentaly deleting default-value formulas?

**matkiros** · 02-10-2012, 03:17 AM

I have a cell containing this formula:

=IF(ISBLANK(A4), "", "0.00")

Basically, if A4 is empty, then the cell is also empty. But if A4 isn't, the default value should be 0.00. The user can edit that value though, and hence can delete what he entered, but even the formula gets deleted from the cell. How do I allow the user to modify the default value without letting him delete the underlying formula in the cell? Thanks

**zbor** · 02-10-2012, 03:22 AM

You don't do this with formula but right click -> Format cell and then format it as number with 2 decimal places.

**matkiros** · 02-10-2012, 03:31 AM

Well, formatting it as a number with 2 decimal places doesn't make it show a default value 0.00 when A4 isn't empty. It only formats the numbers into that cell into a floating-point number with two decimal places, with rounding-off rules.

**TMShucks** · 02-10-2012, 03:39 AM

Unlock all the cells where you allow people to enter data, then protect the sheet with a password.

You can choose whether or not to allow people to select the locked cells.

Note that putting 0.00 in quotes as "0.00" gives you a default text value not a numeric value. Might or might not cause issues depending on what formulae refer to it.

Regards, TMS

**teylyn** · 02-10-2012, 05:17 AM

Hello,

I think the confusion lies in a completely different quadrant.

An Excel cell holds either a formula or a constant (text or number). You cannot use a cell for both. If you enter a number into a cell that has a formula, then the formula will be overwritten.

You could use another cell where the user enters a value, let's say B4, and then your formula would be:

=IF(ISBLANK(A4), "", B4)

When A4 has a value, the formula will return the contents of cell B4. If B4 is empty, it will return a zero. Format the cell to have two decimal figures and teach your users to enter any variation from the default in cell B4.

cheers,

**matkiros** · 02-10-2012, 08:50 PM

^Thanks for clarifying the problem, teylyn. However, is what I want to achieve still possible with macros? I was told I might be able to catch an onChange event or something. Sorry, I have absolutely no background in Excel VBA as my main expertise is in Java.

**teylyn** · 02-11-2012, 04:06 AM

Hello,

yes, it is possible to use Worksheet_Change event to monitor specific cells and do things when the cells are edited. I don't quite understand what you want to achieve, though. Could you step back and explain the bigger picture?

As far as I understand it, the requirements are like this:

- if A4 is empty, cell "X" should be blank.
- As soon as a value is entered in A4, cell "X" should show 0.
- The user should be able to enter a value into cell "X", overwriting the 0
- when the value in A4 is deleted, so A4 is blank again, cell "X" should be blank again as well

The following Worksheet_Change Event performs this. Cell "X" in this case is B4, which is an offset of 0 rows and 1 column starting from A4. Change the offset to suit your needs

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A4")) Is Nothing Then
    If Target = "" Then
        Target.Offset(0, 1) = ""
    Else
        If Target.Offset(0, 1) = "" Then
            Target.Offset(0, 1) = 0
        End If
    End If
    
End If

End Sub

The attached file has the macro installed. Play around with the values in A4 and B4 and see if that is what you need.

Does that give you a starting point?

If you need to enhance this and get stuck, please pipe up. Clearly formulated requirements will help find a solution faster.

cheers,

---------------------
Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste