Solved

Toolbar button to apply custom number format

Posted on 2014-03-28
13
1,134 Views
Last Modified: 2014-03-28
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
0
Comment
Question by:RedLondon
  • 9
  • 4
13 Comments
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
 
LVL 14

Accepted Solution

by:
DrTribos earned 500 total points
Comment Utility
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
 
LVL 11

Author Comment

by:RedLondon
Comment Utility
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
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Oh... lemme check...  BRB
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Yikes - scratch that. It does not work.
Lemme test some more things
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
The steps are outlined here - very different to MS Word which is where I usually play :-)
0
 
LVL 11

Author Comment

by:RedLondon
Comment Utility
Perfect - saving as an Addin did work, I just had to enable the addin at File -> Options -> Addins -> Manage Excel Addins
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
 
LVL 11

Author Comment

by:RedLondon
Comment Utility
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
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
 
LVL 11

Author Comment

by:RedLondon
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now