I am using Excel 2007.

I am wondering if there is a way to modify the "Comma Style" button so that
when I use it, the cell's format changes to:
Number Zero decimals Comma separator -1,234 Negative Style (not red)
--- I use this format most of the time.

Right now, the "Comma Style" button formats the cell with 2 decimals and
parenthesis width before and after the number --- I never use this built-in
format.

One last thing is that I want this to work every time I open Excel, whether
it be done with a macro or not. However, if the only way to do this is with
a macro, I don't want to have to "Enable Macros" every time, as our IS
department won't let us control macro setup. TIA.
--
Brad E.

Private Sub Workbook_Open()

Application.CommandBars.FindControl(ID:=397).OnAct ion = "myCommaStyle"

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

then in a standrd code module


Public Sub MyCommaStyle()

ActiveCell.NumberFormat = "#,##0"
End Sub


--
__________________________________
HTH

Bob

"Brad E." wrote in message
...
I am using Excel 2007.

I am wondering if there is a way to modify the "Comma Style" button so
that
when I use it, the cell's format changes to:
Number Zero decimals Comma separator -1,234 Negative Style (not red)
--- I use this format most of the time.

Right now, the "Comma Style" button formats the cell with 2 decimals and
parenthesis width before and after the number --- I never use this
built-in
format.

One last thing is that I want this to work every time I open Excel,
whether
it be done with a macro or not. However, if the only way to do this is
with
a macro, I don't want to have to "Enable Macros" every time, as our IS
department won't let us control macro setup. TIA.
--
Brad E.

Thanks, Roger, for the reply. After reading your answer, I searched in help
for "Styles" and read some interesting ideas, and incorporated one.

In Home Styles Cell Styles, near the bottom, there are 5 Number Formats.
The two I was interested in we
Comma - Thousands separator, 2 decimals and space for parenthesis.
Comma[0] - Thousands separator and space for parenthisis.

You can right-click one any of the 5 and Modify the formatting which that
button applies. Since the "Comma Style" can be added to the Quick Access
toolbar, I modified this to zero decimals and no space for parenthesis. Now
when I click the "Comma Style" from the Quick Access Toolbar, my number
formats just how I want it to.

--
Brad E.


"Roger Govier" wrote:

Hi Brad

Take a look at Styles on the Home Tab
Use the dropdown and select New Style.
Set it up the way you want, and give it a name if you wish, or just leave it
as the Default Style 1
Once you use it, it will take the first position in the 4 Styles visible on
the sheet, and make it easy for you to apply to any range of cells whenever
you wish.


--
Regards
Roger Govier

"Brad E." wrote in message
...
I am using Excel 2007.

I am wondering if there is a way to modify the "Comma Style" button so
that
when I use it, the cell's format changes to:
Number Zero decimals Comma separator -1,234 Negative Style (not red)
--- I use this format most of the time.

Right now, the "Comma Style" button formats the cell with 2 decimals and
parenthesis width before and after the number --- I never use this
built-in
format.

One last thing is that I want this to work every time I open Excel,
whether
it be done with a macro or not. However, if the only way to do this is
with
a macro, I don't want to have to "Enable Macros" every time, as our IS
department won't let us control macro setup. TIA.
--
Brad E.

Thanks, Bob. I am aware of accessing the Workbook Event code from Excel
2003, but I don't even know how to access this with Excel 2007 (of course I
am not a big user or I would have found out by now). Right clicking on the
Excel button or the MS button does not bring up the "View Code" option.
--
Brad E.


"Bob Phillips" wrote:

Private Sub Workbook_Open()

Application.CommandBars.FindControl(ID:=397).OnAct ion = "myCommaStyle"

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

then in a standrd code module


Public Sub MyCommaStyle()

ActiveCell.NumberFormat = "#,##0"
End Sub


--
__________________________________
HTH

Bob

"Brad E." wrote in message
...
I am using Excel 2007.

I am wondering if there is a way to modify the "Comma Style" button so
that
when I use it, the cell's format changes to:
Number Zero decimals Comma separator -1,234 Negative Style (not red)
--- I use this format most of the time.

Right now, the "Comma Style" button formats the cell with 2 decimals and
parenthesis width before and after the number --- I never use this
built-in
format.

One last thing is that I want this to work every time I open Excel,
whether
it be done with a macro or not. However, if the only way to do this is
with
a macro, I don't want to have to "Enable Macros" every time, as our IS
department won't let us control macro setup. TIA.
--
Brad E.