Activating a Tab on the Ribbon
If you're working with the Office 2007 Ribbon, you may have come to the conclusion that there are areas of, the RibbonX object model which are kind of weak. One of the areas that lacks in the RibbonX API is the ability to programatically activate a specific tab.
The good news is that there is a way to select Ribbon tabs using VBA. The bad news is that we have to do it using SendKeys. Why is that bad news? Well... SendKeys does pretty much exactly that... it sends keystrokes to the user interface. This means that it isn't totally reliable, as users can mess up what windows may be active, send other keystrokes, or whatever. Regardless, it's the only way, so here's how it works:
Activating Built in Ribbon Tabs using VBA
In Excel, to activate the Home Tab using VBA, you'd use:
-
ActivateHomeTab()
-
Application.SendKeys "%H%"
-
End Sub
In Word:
-
ActivateHomeTab()
-
WordBasic.SendKeys "%H%"
-
End Sub
What this does is send the ALT key (using the %), then the H key, then the ALT key again (the final %). The reason that we send the second Alt key is to clear the ALT key from being active. Alternately, you can also use {RETURN} in place of the second %, which will work as well.
You can test this yourself, by pressing those keystrokes manually. This also lets you figure out exactly which keys to use for which tabs. 
Activating custom Ribbon tabs at application startup
Now, for reference, the remaining portion of this post was covered in our book RibbonX - Customizing the Office 2007 Ribbon on page 404 under the heading "Activating a Tab at Startup", and is geared towards Excel. You can sub in your own tabs and adjust the VBA for Word based on what I showed above. (It's slightly adapted here, but only slightly.)
To activate your own tab at startup, you'd use something like the following XML. (Pay attention to the keytip.)
-
<tab id="rxMyTab" label="My Tab" keytip="UN">
And then the following line of VBA gets inserted into the onLoad routine:
-
Application.SendKeys "%UN{RETURN}"
on September 24th, 2008 at 7:22 pm
Ken,
Following your book and the example here, I am using the Application.SendKeys "%UN{RETURN}" to activate a custom tab at startup. Unfortunately, the key tips are displayed. They go away when ALT is pressed. Your book says {RETURN} is supposed to fix that, but that's not how it is working, Any ideas?
Thanks a lot.
kavindra malik
on September 24th, 2008 at 7:44 pm
Hi Kavindra,
Try using Application.SendKeys "%UN%" instead. Let me know how that works.
on September 28th, 2008 at 6:24 am
Ken,
Thanks a lot. I tried but without effect. For the context, I am including my initialize() sub in case something else is causing it to fail.
Really appreciate your help!
--------------------------
Public Sub Initialize(ribbon As IRibbonUI)
Application.ScreenUpdating = False
initializeTabs
Set myContext = New UiContext
myContext.initializeCats
Set myContext.myRibbon = ribbon
Application.ScreenUpdating = True
With Worksheets(myTabs.userView)
.Select
.Cells(1, 1).Select
End With
Application.SendKeys "%UN%"
End Sub
on September 28th, 2008 at 7:28 am
Out of curiosity, have you tested this using the keystrokes yourself? Ie. have you typed in Alt U N Alt to see what happens? Is there a chance that your code should read %un% instead of %UN%?
I assume that everything else is working? The tab shows up on the ribbon?
on September 28th, 2008 at 9:16 am
Thanks.
- I tried replacing the %UN% with %un%, %UN{RETURN}, %un{RETURN} without effect. The XML that defines it would suggest it is UN. I give the top part of the XML below if that helps:
It goes on from there...
- It is otherwise working. I see the ribbon as I want it. The unwanted keytips disappear once return or ALT are pressed.
Perhaps UN keytip is already used and I should use something else like XY.
If something come to your mind, I'd really appreciate it.
Thanks a ton your your comments already!
on September 28th, 2008 at 9:19 am
I guess the interface does not allow for XML to be posted. Let me try again -- inserting extra " and the beginning and end:
" "
There is more, of course, but not relevant to the problems, I think.
on September 29th, 2008 at 7:29 pm
Hi Kavindra,
My blog doesn't play nicely with the XML. I'll email you to get the code.
Cheers,
Ken
on October 24th, 2008 at 8:37 am
To bring closure to this discussion I was having with Ken - I need to report a happy ending!
First of all, Ken was extremely helpful - even offering to look at my code. His helpful attitude gave me a dose of confidence. However, he was not able to reproduce the behavior I was seeing.
In the end, I was able to eliminate the behavior by adding line: Application.Wait (Now + TimeValue("0:00:02"))
prior to sendKey statement. While there is no real logical explanation, but the thought I had was that I was doing a fair bit of work to setup for the menu - and perhaps the sendKey was happening too soon.
Not a particularly satisfying explanation, but this is what I did and what worked for me.
on October 24th, 2008 at 9:28 am
Hi Kavindra,
Glad to hear you got it working. Part of me thinks that it sounds like your PC may not have enough resources for what you're throwing at it, but the other part of me thinks that shouldn't matter. You'd think that Office would be organized well enough not to try and execute a command before the previous ones were completed in this case.
on November 24th, 2008 at 5:39 pm
Here is something that worked for me (for activating the Add-Ins tab)
again not particularly satisfying, but at least there are no explicit waits:
SendKeys "%"
SendKeys "X"
SendKeys "%"
on February 15th, 2009 at 7:03 pm
There is not Application.SendKeys property in Powerpoint2007, How to do it? Thanks
on February 16th, 2009 at 8:23 pm
Hi Stephen,
Just drop the Application. part of it. (Use it like Robert's examples in the comment above yours.)
Cheers,
Ken
on April 15th, 2009 at 4:36 pm
Very strange things happen trying to set focus on custom tab.
I have a dictator app (startFromScratch) with a few custom tabs, one of them is hidden by default. A button on a visible tab brings up a dialog requesting password. With correct password, rxtab_getVisible is set to True. The restricted tab is now visible, but without focus.
I tried using the SendKeys technique with 'ZM' as Keytip but it does not work. It does however write an 'M' in the active cell, and responds to the Enter by moving down one cell.
The starnge thing is that after making the restricted tab visible from code, Alt+ZM does work manually. It also work if I run the password dialog 2nd time (which makes no sense because the tab is already visible).
I tried all suggestions above, and also DoEvents to no avail.
The SendKeys is placed after 'rxRibbon.Invalidate'.
Any idea?
Thanks
Dovy
on April 15th, 2009 at 5:59 pm
Hi Dovy,
I have two potential thoughts, but honestly I don't like either...
Possibly an Application.Wait statement, attempting to delay the SendKeys until the RibbonX has finish exposing the tab? Honestly, I don't think that it would work based on the DoEvents comment. It also shouldn't be necessary and slows your code down.
The other option, which is equally unsatisfactory to me, is to farm out the activation using an Application.OnTime call. This may work, as it wouldn't even start until after the initial routine had come to a complete close. So if there is anything holding it up, this may be resolved.
If either works, please post back and let me know.
Ken
on April 16th, 2009 at 9:33 am
Ken,
Thanks for the speedy response.
ttried ‘DoEvents’, ‘Application.Wait’, ‘Application.ScreenUpdating’ and even to throw in a ‘MsgBox’ but none worked. Surprisingly, it did not even matter if I placed these commands before or after ‘rxRibbon.Invalidate’.
The only thing that did work was your last suggestion:
Application.OnTime Now + TimeValue("0:00:01"), "Ken"
Sub Ken()
Application.SendKeys "%ZM{RETURN}"
End Sub
(I hope you don’t mind the transformation into a Sub). The tab with ‘ZM’ keytip does get the focus, but Excel is still unhappy about something, because it beeps. At this point I don’t know which is worse: let the user make an extra click or let the code set the focus at the price of annoying beep.
Bonus Question 1
In chapter 2 of your book you discuss different ways of handling XML code. I use Visual Studio 2008 Standard as the XML editor and CustomUI for validation, callbacks and pushing it into Excel. That process requires a lot of open, copy, paste, close operations, and is far from efficient. What is your preferred technique? (XML Notepad does not look too friendly, or should I give it another look).
Tip to other readers: Get the book. The instructions for Global Callback Handlers itself (page 157), saved me ton of time, eliminating callbacks clutter. $17 at Amazon.
Bonus Question 2
After using ,how do you add back original Mso controls to the Office menu as custom controls?
Why do I need it?
I am writing a dictator app, but as a developer I need to easily toggle between:
1. user’s customized and very restricted screen,
2. user’s screen plus full normal Excel functionality.
The issue is that after using startFromScratch,getVisible no longer works on Mso controls and tabs. It does work however on custom ones. So I rebuilt all Mso groups into custom tabs and it works fine for tabs.
By the way, for toggeling, I use the same password dialog as I mentioned in the previous post, with a different password. The application in fact has 3 sets of screens: operator (very restricted), manager (extra tab), developer (all of the above + full Excel).
The qustion is how to restructure the original Office Menu so it will respond to getVisible.
Thanks
Dovy
using getVisible no longer works on build-in tabs and controlls.
on April 17th, 2009 at 6:39 am
Sorry for the typos.
1st line in Bonus Question 2 should reed:
'After using startFromScratch, how...'
Ignore very last line after signature
on April 17th, 2009 at 9:03 am
I finally was able to get rid of the beep.
My observation is that KeyTip behaves differently between manual operation and code.
1. Manually: use ALT+KeyTip, execution waits until last character is keyed
2. Code: executions happens once there is unique match
I noticed that when my KeyTip had 2 or 3 chars (beginning with Z), it worked with a beep. When I used only 1 char (Z), there was no beep.
I tried KeyTip of “ZMN†and a button assigned with SendKeys “Zâ€, it worked with beep.
My conclusion is that when run from code, the program responded to the first char (Z) because it was unique match, and then complained about the extra characters (MN) with a beep.
To verify, I tried a KeyTip of “YMâ€, and now it worked without a beep. I started with “Y†because my other custom tabs, to which I did not assigned KeyTips, default to Yn.
Thanks
Dovy
on April 20th, 2009 at 9:15 pm
Hi Dovy,
I can get over the function/sub thing, and I'm glad you got things working!
Re question 1, I use the CustomUI Editor. You can find a link to it here. I still wouldn't say it's efficient, but it's the best tool out there that I've found. (That is not saying it is perfect, in fact, far from it.)
Re Question 2, you don't. If you use startFromScratch, you basically need to rebuild the MSO tabs that you do want. Ridiculous, IMO, but the way it is. I believe I did log a bug about the getVisible on msoTabs when startFromScratch is true, so hopefully it gets addressed at some point.
on June 9th, 2009 at 12:14 pm
[...] Re : VBA et manipulation de ruban I found my answer there : The Ken Puls Blog Blog Archive Activating a Tab on the Ribbon Thanks everybody ! [...]
on May 24th, 2011 at 11:14 am
Used UN{RETURN} and it worked but the {RETURN} key caused issues with my other codes. Replace the {RETURN} with {ESC}{ESC} and it works better.
on July 15th, 2011 at 8:07 am
Just as an FYI, Tony Jollans has a great demo of how to do this without SendKeys here: http://www.wordarticles.com/Shorts/RibbonVBA/RibbonVBADemo.php
on July 15th, 2011 at 8:30 am
Thnks Rory!
on July 17th, 2011 at 9:05 am
I used AJ's approach and used Application.Sendkeys "%G{ESC}{ESC}" ("G" is the keytip for my custom ribbon tab). It works as a standalone macro, but when I link it to Workbook_Open, it doesn't run.
I have put the Application.Wait (Now + TimeValue("0:00:02")) before the SendKeys, but it still doesn't want to run as part of the Workbook_Open. So near yet so far! Still exploring what could be causing the stoppage!
on July 17th, 2011 at 1:47 pm
OK, disregard my posting earlier today. I bought and downloaded your book RibbonX: Customizing the Office 2007 Ribbon into my iPad and got everything working. Wow!
on July 25th, 2011 at 8:05 pm
Glad to hear you got it working, and thanks for buying the book!
on October 18th, 2011 at 3:55 pm
I just use
insertBeforeMso="TabHome"
in the XML, makes it the first ribbon and it's open automatically on any workbook open and requires no code. I'm using 2010, haven't tried in 2007.
Jesse