Adding button to QAT
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Adding button to QAT
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.
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.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Adding button to QAT
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Adding button to QAT
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...)
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Adding button to QAT
In that case, someone else (Jan Karel?) will have to help you. I don't know anything about the XML side of things.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Adding button to QAT
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)
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)
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Adding button to QAT
Have you tried what Ron writes about here: https://rondebruin.nl/win/s2/win008.htm
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Adding button to QAT
@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)
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)
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Adding button to QAT
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.
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Adding button to QAT
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?
Other suggestions? Anyone?
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Adding button to QAT
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.
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Adding button to QAT
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.
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.
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Adding button to QAT
StuartR
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Adding button to QAT
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
I'll try to redo all with a new empty sheet from scratch
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Adding button to QAT
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!
Anyway, seems like it's fixed, thanks!