Format cell to show '-'

G

Gary

Looking for a way to make entries into cell show negative (-) indicator by
default. For instance if '1' is entered, it will display '-1'.
Excel 2003
 
R

Rick Rothstein

Can there be actual negative values in your cells and, if so, how should
they display?
 
G

Gary

they do not have to display as negative, but I want them to function as
negative. If any number is entered, it will be seen as a negative number by
formula referencing that cell.

Thanks,
Gary
 
R

Ragdyer

Formats *do not* change the values in a cell, just the appearance (display)
of the values.

You'll have to work with the formula that references that cell.

With any value entered in A1:
=5*10-Abs(A1)
 
G

Gary''s Student

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
..Value = -.Value
End With
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
G

Gary

I am wanting this feature to apply to certain ranges of cells. I believe
this will affect the entire worksheet?
Regards
Gary
 
R

Rick Rothstein

So... tell us the range of cells you want it to apply to and we will change
the code accordingly.
 
R

Rick Rothstein

.Value = -.Value

While I am not sure I got an answer to my negative number question, assuming
positive and negative values can be entered and that the OP meant it when he
said "If any number is entered, it will be seen as a negative number..",
then perhaps the above line should read this way...

..Value = -Abs(.Value)
 
G

Gary

Need the following:
A4:A27
E2:E8
H3:H9

Rick Rothstein said:
So... tell us the range of cells you want it to apply to and we will change
the code accordingly.
Click to expand...
 
R

Rick Rothstein

Try this code then...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A4:A27,E2:E8,H3:H9")) Is Nothing Then
With Target
.Value = -Abs(.Value)
End With
Application.EnableEvents = True
End Sub

Note that in keeping with my response to Gary''s Student, I changed the
assignment line to make any entry, positive or negative, into a negative
number.
 
R

rslc

Gary said:
Looking for a way to make entries into cell show negative (-) indicator by
default. For instance if '1' is entered, it will display '-1'.
Excel 2003
Click to expand...

you can right click the cell and use the format menu format cells on number
tab click custom and in the type box enter -0 then ok. you can copy format
to as many cells as you like


cheers

rslc
ps i use something similar to display temperature in celcius
 
Top