Adding button to QAT

User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Adding button to QAT

Post by ErikJan »

I've been playing with the Ribbon a while back and with the help of "Office RibbonX Editor" and info from "Ron de Bruin"'s website I managed to get some things done.
For a small new project (in Excel) I'd like make a small add-in that adds a button on the QAT (which then runs my VBA code when pressed).
I looked around but found no template XML file that gives me clues as to how this can be done. Anyone who can help me to get started, with the XML, I think I can work out the matching VBA code.

User avatar
HansV
Administrator
Posts: 69393
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adding button to QAT

Post by HansV »

Open your add-in as a workbook.
When you add a button to the QAT, you can specify that it will be stored in that workbook.
It will automatically be saved in the add-in and be available when the add-in is loaded. No VBA needed.
Regards,
Hans

User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Adding button to QAT

Post by ErikJan »

Not sure but I see the macro, when I select on the right side that this is not for all documents but just for this WB the macro disappears from the left box (so I can't add it...)

User avatar
HansV
Administrator
Posts: 69393
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adding button to QAT

Post by HansV »

In that case, someone else (Jan Karel?) will have to help you. I don't know anything about the XML side of things.
Regards,
Hans

User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Adding button to QAT

Post by ErikJan »

Thanks Hans, I Googled some more but can't find a solution. It's odd that I can't add VBA code to a QAT that is local to the document that has that code (so it only works when the file with the code is loaded).
I'll guess I'll add it to all first and then convert my code to an add-in (but again, if somehow the add-in is not loaded and someone clicks the button on the QAT, there will be an error - or the add-in will load).
Indeed, the XML option could help, that was my initial thought but I liked your suggestion better (as that seems much more simple)

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 650
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Adding button to QAT

Post by Jan Karel Pieterse »

Have you tried what Ron writes about here: https://rondebruin.nl/win/s2/win008.htm
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Adding button to QAT

Post by ErikJan »

@Jan Karel: Thanks, yes, I looked there. Ron says: 'In the "Customize Quick Access Toolbar" dropdown choose "For OneWorkbookQAT.xlsm" when you add the controls that you want in the QAT.'

For starters (and as I found out and indicated): that does not seem to work...

Here's what I did:
1. Create an XLSM with a VBA module and one sub (not private)
2. Go to QAT, select "More Commands"
3. In the left dropdown (by default "Popular Commands" I select "Macros")
4. My subroutine is now visible under "<Separator>"
5. In the right dropdown (Default: "For all documents (default)") I select "For MyFilename.xlsm"
6. The moment I do that, my subroutine disappears from the left dropdown (so I can't add it)
7. I tried this one two different systems (boot with -different versions of- MS365)

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 650
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Adding button to QAT

Post by Jan Karel Pieterse »

For me the button stayed there, but when I subsequently saved as xlam and then opened the xlam, the button was there but pointed to the xlsm. Duh.
Last edited by Jan Karel Pieterse on 21 Nov 2020, 18:06, edited 1 time in total.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Adding button to QAT

Post by ErikJan »

Very strange... Still trying to find out how I can run my VBA code from a button on the QAT in Excel.

Other suggestions? Anyone?

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 650
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Adding button to QAT

Post by Jan Karel Pieterse »

The only way I can make this work is by first creating the add-in, loading the add-in and then modifying the QAT. Then I can add macro's to the QAT, but only ones which are also available in the alt+F8 dialog. So macro's unavailable in alt+F8 are also unavailable to add to the QAT.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 650
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Adding button to QAT

Post by Jan Karel Pieterse »

Not true!
1. Create your workbook with code
2. Save and save-as add-in
3. Open xlam
4. Go to VBE, open ThisWorkbook of add-in
5. Change IsAddin property to false
6. Go to Excel, modify QAT, add button to QAT only for add-in
7. Go back to VBE, set IsAddin back to True
8. Save add-in.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
StuartR
Administrator
Posts: 11044
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Adding button to QAT

Post by StuartR »

:clapping:
StuartR


User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Adding button to QAT

Post by ErikJan »

I stlll fail in step 6: the moment I select 'only for add-in' the macro disappears...

I'll try to redo all with a new empty sheet from scratch

User avatar
ErikJan
5StarLounger
Posts: 922
Joined: 03 Feb 2010, 19:59
Location: Terneuzen, the Netherlands

Re: Adding button to QAT

Post by ErikJan »

Update: If I create an new WB from scratch and copy the VBA code it does work... Strange...

Anyway, seems like it's fixed, thanks!