Free Microsoft Excel 2013 Quick Reference

adding custom button to quick access toolbar

I just entered the world of excel 2007 (and win7) kicking and screaming.

I have a macro that I want to add as a button on the quick access toolbar. I would want this macro to be available from the QATB (is that what yall call it?) anytime that I open excel.

How would I go about doing this? BTW - it doesnt really matter where the button is located just as long as I can have super fast access to this macro (actually I have many many I want to add).

Post your answer or comment

comments powered by Disqus
I use Excel 2007. I have created 4 buttons on a sheet named JUN, a worksheet for the month of June. One runs a custom report, one searches for values using a userform, one moves data and one access the web. All these buttons are useful, imporatant and necessary for me to perform certain operations I have to copy each button and its code over and over for every new worksheet month(12 times for 12 months) so I can use them .

Is there a way to create or move these buttons to the Quick Access Toolbar and made smaller so they will only have to be created once and be visible as custom Quick Access Toolbar buttons to EVERY sheet in the workbook?

It's fine with me if I have to use VB programming - I just need a toolbar(preferably the Quick Access Toolbar) with these and any new buttons I may want to create, visible to all worksheets in my workbook.

Thanks for all your help on this.

In Excel 2003 I was able to add a menu and then add macros to it. I was also
able to add macros to custom buttons to any toolbar. In 2007 I can create a
custom button within a workbook to which I can add a macro, but how do I add
it to the ribbon bar? How do I add a button with a macro assigned to it to
the Quick Access Toolbar? Incidentally, I'm still using Windows XP. Thanks

When I add a command to the quick access toolbar, there is no option to
associate it with only the current document. The 'For all documents
(default)' selection is the only available choice in the Customize Quick
Access Toolbar drop down.

Is there something I am missing? How can I have a command show up in the
qualick access toolbar only when that specific workbook is opened?

Thanks in advance,

I have suddenly started to lose the customizations on my Quick Access Toolbar
when I reopen files.

I add (1) built-in commands and (2) custom commands with assigned macros to
specific workbooks. I then save and later reopen. The new commands always
used to appear on the QAT.

Now when I reopen the workbook, the QAT reverts to the default setting with
the 3 commands: Undo, etc.

Any explanations?



I got a question about adding a custom button to the Excel toolbar. I know it is simple, but something does not work in my code below. Could please anyone help!

    Dim ReportButton As MsoControlType 
    If MsgBox("Would you like to run a report?", vbYesNo, "Confirmation") = vbYes Then 
        Run "MenuMacro" 
        Application.CommandBars("Visual Basic").Controls.Add Type:=msoControlButton, ID:=2950, Before:=7 
        Set ReportButton: = msoControlButton 
        With ReportButton 
            ReportButton.Caption = "Run Report" 
            ReportButton.OnAction = "MenuMacro" 
        End With 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I am trying to achieve with this code is if user does not want to run the report when prompted and clicks "No", a custom button is created, and user can run report at a later time by clicking on this button.

Thanks for your help!


Happy Friday everyone!

I'm using some code posted by Tom Urtis back in December for a drop down navigation menu. Unfortunately, in Excel 2007 it puts this drop down in the "Add-ins" tab so it is not always visible. Is it possible for me to add this drop down navigation menu to the Quick Access Toolbar?

This code is in a module:

 Option Explicit

Private Sub ResetMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Sheet selector").Delete
End Sub

Private Sub MakeCBO()
With Application
.ScreenUpdating = False
Run "ResetMenu"

Dim cboSheetz As CommandBarComboBox, ws As Worksheet

With .CommandBars("Worksheet Menu Bar")
Set cboSheetz = .Controls.Add(Type:=msoControlComboBox, before:=.Controls.Count)
End With

With cboSheetz
.Caption = "Sheet selector"
.OnAction = "mySheet"
End With

For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then cboSheetz.AddItem ws.Name
Next ws

cboSheetz.ListIndex = 1

.ScreenUpdating = True
End With
End Sub

Private Sub mySheet()
With CommandBars("Worksheet Menu Bar").Controls("Sheet selector")
End With
End Sub
While this code is placed in the workbook object:

 Private Sub Workbook_Open()
Run "ResetMenu"
Run "MakeCBO"
End Sub

Private Sub Workbook_Activate()
Run "ResetMenu"
Run "MakeCBO"
End Sub

Private Sub Workbook_Deactivate()
Run "ResetMenu"
End Sub

Thanks for looking and have a good weekend,


First, I'm new to Office 2007
Second, I'd like to add macro buttons to the quick access toolbar that are associated with a certain worksheet. That is they should be available only when the worksheet is active. I open the Quick Access Toolbar and select "Macros" in the "Choose Commands From" - I can see the macros I want to include preceded by the 'ProgramName!...' prefix. Note: there are several others that are in ProgramName, but do not have this prefixIf I select "For ProgramName:" from the "Customize Quick Access Toolbar" drop-down, all these prefixed macro names disappear, but the non-prefixed ones stay.Both sets of macro are only in the ProgramName file. They are all public Subs with no arguments.So, what causes the difference? Why can't I see all my macros? Thanks and a tip 'o the hat,


In Excel 2007, is it possible to add Advanced Options (Office Button/Excel Options/Advanced, Editing options) to a Quick Access Toolbar? I'm particularly interested in "After pressing Enter, move selection..." and "Automatically insert a decimal point".

I have a workbook with several macros to automate repetitive tasks. The tasks
are so repetitive that I added the macros to the Quick Access Toolbar. I
added the buttons "for this workbook only", because I don't need them in
every workbook (and they aren't available in any other workbooks). I save the
file, close it, and re-open it. I then see that the "this workbook only" part
of the QAT is completely gone.

I assume this is a bug, because if it were by design, then there would be no
point in allowing macros to be added to the toolbar. Also, I have added
macros to QATs on other computers. The problem seems limited to my computer.
The problem is NOT limited to one workbook; I can reproduce the issue at will
with any XLSM file.

Many internet searches have yielded no help. Does anyone have any ideas?

Thank you.

In previous versions of Excel it has been possible to edit the image which
appears on a button in the toolbar. I used it when I assigned macros to
buttons. In Excel 2007 I have found that only the Quick Access Toolbar is
(readily) customisable but the choice of images is poor and I can't find a
way to edit / create new ones.

I just bought Office 2007, and I have two questions regarding Quick Access
1. I use Excel frequently, and I am taking advantage of the quick access
toolbars (below the ribbon). Unfortunately, when you insert commads to QAT,
command icons without pre-assigned graphic appear as "green dots", i.e. paste
formula and paste value commands. Is there a way to change this - I don't
know why MS do not provide tools to solve this proble, like custom icon

2. Under the same heading, when I add command in "Customizing the QAT"
section, I noticed that "Modify" button (located next to "Add") button is
grayed out. I suspect that this will actually help me with my question #1,
but I don't know how to access this. Can someone help me?

Really appreciate it!

P.S. I really don't want to use macros or programs unless MS designed it
and included it in the patch.

Hi, when adding icons to the Quick Access Toolbar there is not always an icon
next to the command so it adds a green disc. This is greyed out until the
button is useable (fair enough.) If you add a second command it puts another
green disc eg "Paste Values" and "Paste Formula." I can't find anyway to make
these distinguishable from one another apart from remembering the one on the
left is "values" etc. Not a problem till I've added about 5 icons and have 5
green discs! Does anyone know a way to customise the icons?




I have an excel document in which I added three buttons to a toolbar which
are assigned to three different Macro's.

I then email a copy of the worksheet to my colleagues and when they open the
file, the buttons that I originally created no longer appear where I put them.

How do I ensure that any customization that I do of an Excel worksheet stays
permanently on the worksheet when it's emailed to others?



I have added a couple of macros to the quick access toolbar. However, none of the extra icons they provide are close to describing what these new buttons do. Is there a way to add more icons? There must be a directory where excel stores these, and where one could add an extra image file in to have it appear when you click "Modify".


Hi, I hope someone can help

I have created a number of Excel chart templates (e.g. pie, line, bar) as our CEO likes all graphs to look a particular way on reporting.

I want to add these templates as shortcuts on the quick access toolbar so any new chart I create can be changed to the template style with the click of a button.

Is this possible? Ive noticed I can add the "Change Chart Type" command to the toolbar but I cant find a way to add the individual templates to the toolbar.

PS I have the templates set as the default chart style but I want the option above too if possible.

Thanks so much

Demo: Place your favorite commands on the Quick Access Toolbar

There's a fast way to put the commands and buttons you use most often within easy reach — put them on the Quick Access Toolbar.

I upgraded to Office 2007 a few months ago & used the Quick Access Toolbar to
overcome some of the pain of transition. The company Windows 2000 Server has
now been replaced by a Windows Server 2003 & my Quick Access Toolbar has been
reset to the default.

Can anyone tell me how to find my old Toolbar, or tell me where to find the
command that allows me to post a spreadsheet to an Outlook folder. Ive looked
thru every command & can't find it again, & that was the only way I found of
saving an actual spreadsheet (not a shortcut) in an Outlook folder.

I'm using XP Pro, SP2 on my desktop PC.


There is a way to display the file name in the quick access toolbar, but I am
not sure which icon to add into the toolbar customization. It's not "file
name, Sheet Name or File Path". I used to have this set-up on my old
computer but can not remember how to to do it.

Can someone help?



I've just migrated (migrained should be the word) to Win Vista, with Office
2007, and I'm trying to set up my excel so that I can work faster- as I did
in my previous versions.

In the previous versions I had made meny bars that I customized and that
worked better for me with the commands I use most. I was doing the same
with my new Excel, but one cannot make several menu bars any more. At
least, I don't see how. So I tried to put all these commands in the quick
access toolbar, but the toolbar doesn't split it up into different lines but
now I have a huge quick access toolbar wich isn't any good either.

I can't say I like the new layout. Even though I have a relatively nice
screen (large), I try to keep icons small, I try to keep the clutter out,
and now it seems there is so much more clutter and big icons that I never
use. So I minimzed the "Ribbon" because that is total clutter to me.

How on earth do I break up the quick access toolbar into segments so that
this one can fit several (2 or 3 or....) lines?

Thanks for the help.


I have to customize the quick access toolbar each time I start Excel
2007 as the changes are not saved from one session to the next. I do
not remember this happening in previous versions.

Am I doing something wrong or is that just the way it is?



In the 2003 version of excel you could add the patterns as a button like the
colour button and text colour button. I cannot find this function in the
commands to enable me to add it to the quick access toolbar. Has anyone else
found it and if so could they let me know where it is?

does anybody know how to create a custom button on the formatting toolbar
that converts data into euro and without decimals? thanks!

My company recently upgraded to Office 2007.

As such, am learning about the Quick Access Toolbar (QAT).

I have customized my QAT to contain the things I need.

PROBLEM: When I use a differant computer, my customized QAT does not follow me, it displays the standard QAT.

QUESTION: How can I get my customized QAT to be transportable? Is there a file that is updated for the QAT that I could copy and use?

I am placing macros on the Quck Access Toolbar. Excel allows me to select an icon for each macro from over 100 options. Oftentimes, the listed icon options do not graphically reflect the purpose of the macro. How can one edit the existing icons or add custom icons to use?


No luck finding an answer? You could always try Google.