Button for custom number format

[Brad E.]

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.

 

[Bob Phillips]

Private Sub Workbook_Open()

Application.CommandBars.FindControl(ID:=397).OnAction = "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

 

[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 were:
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.]

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.