Cell Masking in Excel

[villy]

Hi All,

I'm here again as this is always my last resort, I already look through different post about something called mask display in excel.
Is it possible to mask the actual value of a cell?
For ex. If I have a value of 1 in "B1" as a result of a function for instance (SUM), Is it posibble that in cell "B1" instead of displaying 1 I want the display to be 0 for example.. Is there a way to do it (even in VBA) or is there so called cell masking?

I know this is something weird but if there is anyone with a brilliant ideas much appreciated. thanks

regards,
villy_voo

________________________________
"Share the blessing and you'll be more blessed."

 

[Jaafar Tribak]

You can try a number custom format such as "0" from Format>Number>Custom

 

[villy]

I don't think it is working like that...
You cannot just substitute single '0' otherwise there is no effect in formatting...
Any other idea?
Thanks

 

[RoryA]

Jaafar's suggestion would display 0 in the cell which is what you asked for, as far as I can see. Perhaps you could explain why that is not what you want.

 

[villy]

yes perhaps it is ok showing in the cell '0' but how can I change the display in formula bar to be the same or just make it blank?

Thanks

 

[RoryA]

Set the cells Protection to Hidden in the format cells dialog and then protect the worksheet.

 

[villy]

I see that's what exactly I did but still I'm not happy with it. I guess no more solution to that..
One more question if you don't mind...
Can I use "0" also as numberformat if there is no input in a cell (let say it is blank cell or whenever I delete the value of a cell I want it to display also 0.

Thanks

 

[RoryA]

No. A Number format will only work if there is something in a cell.

 

[villy]

OK thanks all for your thoughts.. Really glad there is always somebody better than me...
I guess I need just to do a little trick in this...
See yah guys 'till next.
God bless
Thanks again

----------
villy_voo

 

[Jaafar Tribak]

I see that's what exactly I did but still I'm not happy with it. I guess no more solution to that..
One more question if you don't mind...
Can I use "0" also as numberformat if there is no input in a cell (let say it is blank cell or whenever I delete the value of a cell I want it to display also 0.

Thanks

You may want to try some little vba for this :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.NumberFormat = """0""" Then
        If IsEmpty(Target) Then
            Application.EnableEvents = False
            Target = 0
            Application.EnableEvents = True
        End If
    End If
End Sub