Format cell to show '-'
 

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

Format cell to show '-'
 

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Gary" wrote in message
...
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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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

--
Gary''s Student - gsnu200852


"Gary" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200852


"Gary" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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

--
Rick (MVP - Excel)


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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200852


"Gary" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

.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)

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200852


"Gary" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200852


"Gary" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

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.

--
Rick (MVP - Excel)


"Gary" wrote in message
...
Need the following:
A4:A27
E2:E8
H3:H9

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200852


"Gary" wrote:

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Gary" wrote in message
...
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

Format cell to show '-'
 

"Gary" wrote in message
...
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

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

Format cell to show '-'
 

This only changes the appearance of the number, not its function.

Gary

"rslc" wrote:

"Gary" wrote in message
...
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

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