Toolbar button to apply custom number format

I have a custom number format in Excel, which I can apply to a cell by right clicking, choosing Format Cells, then on the Number tab selecting Custom and entering/selecting the format #,##0.00 ;(#,##0.00)
 
How can I add a toolbar button in Excel 2013 to format cells that way without having to go through those three steps?  

Quick access toolbar or Ribbon, no preference - whichever is easier
LVL 11
RedLondonAsked:
Who is Participating?
 
DrTribosConnect With a Mentor Commented:
Or... (learning on the fly)  to apply the formatting to a selection of cells...

Sub applyFormat()
Dim selRng As Range
 Set selRng = Selection
    selRng.NumberFormat = "#,##0.00 ;(#,##0.00)"
End Sub

Open in new window

0
 
DrTribosCommented:
The VBA would be:

Sub applyFormat()

ActiveCell.NumberFormat = "#,##0.00 ;(#,##0.00)"


End Sub

Open in new window


You can add to QAT easiest
0
 
DrTribosCommented:
Steps:
1. Alt + F11 to open the VBA Env
2. Paste the macro into ThisWorkbook
3. Save
4. On ribbon - right click, select Customise Quick Access Toolbar
5. In "Choose Commands From" select Macros
6. Select the macro "applyFormat"
7. Add to QAT
8. Assign a button & rename & OK
9. select a cell with numbers and press your new button
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
RedLondonAuthor Commented:
That's great - works perfectly, but the file I saved the macro in opens every time I press the button, is there a way to stop that (or to close it automatically)?

I had to save the workbook in a "Excel workbook with macro support" filetype and click to 'Enable' the macro the first time I opened it, but the PC remembers that I trust that file afterwards - that's fine, but the file keeps opening so it'd be great if we could stop that
0
 
DrTribosCommented:
Oh... lemme check...  BRB
0
 
DrTribosCommented:
I'm not sure of the BEST way to do this but... try:
Put the macro in to an otherwise empty spreadsheet, do the QAT thing, then Save As - using "Excel Add-In"

the Save As dialog will then take you here:
"C:\Users\Red London\AppData\Roaming\Microsoft\AddIns"

I have not actually tested this in Excel (I have done similar in PPT) but it should then become global...
0
 
DrTribosCommented:
Yikes - scratch that. It does not work.
Lemme test some more things
0
 
DrTribosCommented:
The steps are outlined here - very different to MS Word which is where I usually play :-)
0
 
RedLondonAuthor Commented:
Perfect - saving as an Addin did work, I just had to enable the addin at File -> Options -> Addins -> Manage Excel Addins
0
 
DrTribosCommented:
Oh... cool I didn't get it to work.  I ticked the box but it seemed to open the original file as you mentioned.... perhaps I'm just tired.  

Glad you got it working
0
 
RedLondonAuthor Commented:
I deleted the QAT button I created first, deleted the Macro enabled Workbook, then did the addin - I think your button is still working from the first route rather than the second

Thanks again
0
 
DrTribosCommented:
Doh - I forgot to undo my first QAT... yep must be tired... I also got it working in the personal book as per the article.  Not sure of the pros and cons of either approach.
0
 
RedLondonAuthor Commented:
Just added it as an Add-in on the customer's PC and he's happy with it, so I'm happy.

I'll be re-reading this in a couple of years when he gets a new PC and complains that that button is missing, wondering how the hell I got it there for him in the first place!

(I'd already created him a custom group on his Home ribbon tab, so his button is happily on that now)

Many thanks, go to bed if you're tired :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.