Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I format a cell value to show kg? | Excel Discussion (Misc queries) | |||
format cell to show leading zeros and make cell a three digit fiel | Excel Worksheet Functions | |||
Format cell to show colon | Excel Worksheet Functions | |||
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Discussion (Misc queries) | |||
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Worksheet Functions |