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:

[code]ActivateHomeTab()
Application.SendKeys "%H%"
End Sub[/code]

In Word:
[code]ActivateHomeTab()
WordBasic.SendKeys "%H%"
End Sub[/code]

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.)

[code][/code]

And then the following line of VBA gets inserted into the onLoad routine:

[code]Application.SendKeys "%UN{RETURN}"[/code]

Share:

Facebook
Twitter
LinkedIn

65 thoughts on “Activating a Tab on the Ribbon”

  1. 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

  3. 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

  4. 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?

  5. 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!

  6. 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.

  8. 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.

  9. 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.

  10. 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 "%"

  13. 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

  14. 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

  15. 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.

  16. Sorry for the typos.
    1st line in Bonus Question 2 should reed:
    'After using startFromScratch, how...'
    Ignore very last line after signature

  17. 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

  18. 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.

  19. Pingback: VBA et manipulation de ruban - Excel Downloads Forums

  20. I have your book, and it is great!

    I am using Access 2007, and SendKeys is not supported. If not, how do I (page 404) activate a tab at startup???

    Thanks,
    Frank

  21. The keytip option doesn't seem to be working for me at all. My tab XML keyword includes keytip="TU" but when the add-in loads and I press Alt, the tab persists in displaying "Y1" instead of any text I specify for the keytip.

  22. 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.

  25. 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!

  26. 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!

  28. 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

  29. I have used your RibbonX book as my bible for building custom ribbons in Excel 2007 for several years, and have been extremely pleased with the results. The only modification I have made to the dreaded sendkeys method has been the addition of "SetActiveWindow Application.hwnd" as a belt-and-suspenders approach to combat window focus issues. Within 2007, it has been xtremely successful. Fast-forward to 2010, and it all falls apart. The KeyTip property in the customui.xml file seems to be failing, and all of my custom tabs (I have several) are defaulting to Y1, Y2, etc. Any advice or insite would be helpful.

  30. Using SendKeys to do this was resetting my keyboard state, so numlock would go off every time I opened my Excel program. In place of SendKeys, I now use the windows api function keybd_event and that works well -- the keyboard state is not reset when the macro runs.

    Private Declare Sub keybd_event Lib "user32" ( _
    ByVal bVk As Byte, _
    ByVal bScan As Byte, _
    ByVal dwFlags As Long, _
    ByVal dwExtraInfo As Long)

    ' Constant declarations:
    Public Const VK_NUMLOCK = &H90
    Public Const VK_SCROLL = &H91
    Public Const VK_CAPITAL = &H14
    Public Const VK_TAB = &H9
    Public Const VK_MENU = &H12 ' ALT
    Public Const VK_LSHIFT = &HA0
    Public Const VK_RETURN = &HD
    Public Const VK_Y = &H59
    Public Const VK_U = &H55
    Public Const KEYEVENTF_EXTENDEDKEY = &H1
    Public Const KEYEVENTF_KEYUP = &H2

    Private Sub Bring_Tab_To_Front()
    ' bring custom tab to front
    ' Alt-Y is a hot key attached to one of the keys in the LotStatus menu
    ' use keybd events to simulate pushing Alt-shift-Y return to bring the menu to the front.
    '
    keybd_event VK_MENU, 0, 0, 0
    keybd_event VK_LSHIFT, 0, 0, 0
    keybd_event VK_Y, 0, 0, 0
    keybd_event VK_Y, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_LSHIFT, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_RETURN, 0, 0, 0
    keybd_event VK_RETURN, 0, KEYEVENTF_KEYUP, 0
    DoEvents
    End Sub

  31. Hi Ken,
    I posted on Tech Support Guy and your link was give to me.
    I don't like the sendkeys myself, I was also directed to take a look at a Word sample that Tony Jollans posted and modified his code to do some 'reverse engineering'
    http://www.wordarticles.com/Shorts/RibbonVBA/RibbonVBADemo.php

    I attached my sample file to my post
    http://forums.techguy.org/business-applications/1125906-excel-2010-trigger-macro-when.html

    I'd appreciate your time if you could take a look.

    Thansks in advance,

    Hans

  32. Hans, thanks for the link. That's an awesome example of porting Tony Jollans use of the Ribbon Extensibility API to pull this off. (For others, a 32/64bit compliant working example is now hosted on the Tech Support Guy site at the link above.)

  33. Hi Guys... I ´ve gave up using SENDKEY ... so I had success with : (Excel 2010)

    Sub rxIRibbonUI_onLoad(Myribbon As IRibbonUI)
    Set grxIRibbonUI = Myribbon
    grxIRibbonUI.ActivateTab ()
    End Sub

  34. By the way ... grxIRibbonUI.ActivateTab (put here the ID of tab using quotes)

  36. This worked perfect. I'm so impressed I just went to Amazon and bought the book. Looking forward to having a lot of fun with the ribbon 🙂

  38. I have what I think is a simple objective that someone would have done by now, but could not find a straightforward example online. This thread comes closest to discussing it, but your suggestion, as logical as it is, does not work (and I came to the conclusion to try it before I had even found this thread...)

    In VBA, I simply want to activate a menu group on the Excel Ribbon; so instead of the "Home" group showing, I want the "Data" group showing in certain circumstances.

    So I Tried Sendkeys "%D" (Alt-D, for the "Data"...), and Sendkeys "%{D}" -no luck (for some very strange reason, it triggers a Pivot Table wizard...)

    I can see the Data menu in the "CommandBars" collection, but it offers no way to activate that menu tab.

    Now there IS an "ActivateTab" command for the ribbon UI; but no straightforward means is discussed for getting Excel's ribbon (you'd THINK they would have given you something like Application.Ribbon!)

    I think there might be a way to do it using CommandBars.ExecuteMso, but the command is HORRIBLY documented.

    Any ideas for activating a menu group on the (Excel Office) ribbon??

  39. Hi Steve,

    You bet. If you press the Alt key, you'll see that the Data tab's accelerator is actually A not D. The reason for this would be that Alt+D was already used for that PivotTable Wizard, and the Excel team didn't want to break backwards compatibility. If you update your macro to use %A% I would think it should work.

  40. Playing with it, I finally came up with a workaround:

    SendKeys "%h{RIGHT}{RIGHT}{RIGHT}{RIGHT}"
    (orient to the home menu, then go fourth menu to the right...)

    Absolutely nutty that they don't give you (i.e., at the least, document!) a way to activate a menu...

  41. I'd be a bit careful with that one. If a user creates their own tab and puts it between the Home and Data tabs, yours will come up with the wrong tab. Maybe a long shot, but I think it would be better to go the %A% route.

  43. (Sorry, we crossposted...)

    Re: %A%; great! Clearly, that's preferable.

    But I still maintain that it's nutty that they don't give you direct hooks to Excel's ribbon with ActivateTab, etc...

  44. With %A, less "jitter" on the menu too.

    Where are the menu shortcuts documented? I don't have to know, but I was checking if I am overlooking some major info resource...

  45. The VBA SendKeys command actually does exactly that... it just sends keystrokes to the application.

    If you go to Excel and press your Alt key, you'll see letters pop up on every ribbon tab. The Data tab is A. Press A and it will activate the tab, but leave the accelerators for all the other commands. If you press one of those letters... 🙂

  46. I knew about Sendkeys; I just didn't know the %A mapping
    (not exactly intuitive, you'd have to admit...)

    I just figured seeing as how they have these backward compatibility mapping issues, they would have issued a clear list as to what's mapped to what...

  47. Not intuitive, I agree, but easy to discover the route once you realize you're just subbing % for Alt and then recording the series of keystrokes needed to drill into the path you want.

  48. Hi Ken,

    I was trying to access the Format context ribbon tab for charts. To do it manuallly I select the chart, press ALT and can see that I need to press "JA" to get to the Format tab. However when I try to replicate this using VBA it doesn't work. After much trial and error I realised that the context menu wasn't appearing after I select the chart using code. So if I only run this code snippet the context sensitive chart tab does not appear:

    set co = Sheet1.ChartObjects("Chart 1")
    set c = co.Chart
    co.Select

    At this point there is no context sensitive tab displayed on the ribbon so I can't use SendKeys to get to it. Any idea why this is happening?

    The alternative at this point is to send CTRL+1, which does bring up the Format task pane, but it doesn't have the focus. The chart still has the focus, so if I send Tab etc it just cycles through the chart elements.

  49. Hi Stefan,

    What version of Excel are you using? In Excel 2013, your code selects the chart and displays the context sensitive ribbon...

  50. It is Excel 2013. If I run the code below then all is fine, the context menu appears:

    Set co = Sheet1.ChartObjects("Chart 1")
    co.Select

    If I run this:

    Set co = Sheet1.ChartObjects("Chart 1")
    co.Select
    Application.SendKeys "%"

    then the key tips (letters) are highlighted on the ribbon, but the context menu isn't there. Even if I add some DoEvents and application.wait it still doesn't work.

  51. Right, I see what you mean. Unfortunately, I don't have an answer for you... but let me see if I can pull in someone who might know...

  53. Having seen the ability to use ActivateTab in Excel 2010 I assumed this would also be available in office 2013.

    My code

    Public Sub RibbonOnLoad(ribbon As IRibbonUI)
    ' Store pointer to IRibbonUI
    Set YourRibbon = ribbon
    Application.CommandBars.Item("Ribbon").visible = False
    Application.CommandBars.Item("Ribbon").visible = True
    ThisWorkbook.Sheets(1).Range("AA1").Value = ObjPtr(ribbon)
    End Sub

    then

    Sub SelectRibbon()
    If YourRibbon Is Nothing Then
    Set YourRibbon = GetRibbon(ThisWorkbook.Sheets(1).Range("AA1").Value)
    YourRibbon.ActivateTab ("Velacc")
    Else
    YourRibbon.ActivateTab ("Velacc")
    End If
    End Sub

    However all I can get as a result is "Invalid Procedure call"

    Has this functionality changed again in XL2013 or been lost? Or am I doing something wrong?

  54. I don't believe that any commands were removed in 2013, no. Where is the error showing? I'm wondering if it's in part due to the GetRibbon piece. Afaik, that's never been a supported hack for recapturing a lost Ribbon.

  55. Hello,

    I have built a custom excel ribbon. Part of this ribbon has buttons from all the other ribbons that are used frequently. I have code below just to pop open one of the windows however the code then moves me to that ribbon. I want to stay on the custom ribbon and just have that window pop open without leaving the ribbon I was on. Is that possible?

    Sub Protect()

    Application.SendKeys "%{R}{P}{S}"

    End Sub

  56. Hey Anthony,

    It's been a LONG time since I've done any of this kind of work, but try this:

    Sub Protect()
    Application.SendKeys "%R%P%S%"
    End Sub

    That may do it. The issue here is that Excel WILL activate the new tab, you just need to reactivate your custom one. So you're looking for the commands that get you back to where you were. Press ALT and then follow the keystroke trail to where you want to be. Then you just need to work out the key stroke code to get there.

  57. Hi guys,

    This is work for me,
    Public Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set objRibbon = ribbon
    ribbon.ActivateTab ("customTab")
    end sub

    Thanks to you all

  58. Friends,
    I prepare a auto_open for activating a tab on the Ribbon
    I Work with Excel 2016

    sub auto_open()
    application.sendkeys "%Y2%"
    end sub

    This code is not working.
    Please, help me in this tema
    Many thanks
    Joel

  59. Hi Joel,

    Is Y2 the tab name when you press the ALT key?

    Generally I tend to use the Workbook_Open event instead of Auto_Open, but I'm not sure if that matters in this case.

  60. Hi Ken!!!
    Many thanks for your answer
    It does not have to be the solution with auto_open. Can be another, the important thing is that it works
    If I pulse shift F6 (My Excel is Excel 2016) all the letters in the tab appear. Tab that I want to activate is Y2
    I need to activate Y2 when I open the file in Excel.
    Thank you very much for your help
    Regards

  62. Ken,
    Excuse me, the Tab letters actívate with Shift+F6 in Excel 2016
    "{F6}Y2" This would change then?

    Ken, when you are going from one sheet to another, if this macro below is on each sheet, it activates the Ribbon tab automatically. But only serves between leaflets. This macro work very well.

    Private Sub worksheet_activate()
    Application.SendKeys "%Y2%"
    End Sub

    Given that Y2 is the Ribbon tab

    Regards

  63. I had no idea that SHFT+F6 would do this. Seems to be the same thing as ALT.

    So yes, SHIFT+F6 would be "+{F6}Y2"

    But "%Y2%" should do the same thing.

  64. Ken,
    Confirmed ALT = shift + F6 for leters of TAB

    The complete macro would be?:
    Sub auto_open()
    Application.SendKeys "+{F6}Y2"
    End Sub

    Is there any other way to activate Tab, when the Excel file is opened?
    Such as through Custom UI?

  65. Hi Naushad Ali,

    Where do you put this code?

    Public Sub OnRibbonLoad(ribbon As IRibbonUI)
    Set objRibbon = ribbon
    ribbon.ActivateTab ("customTab")
    end sub

    I tried it as standalone module and in PERSONAL.XLSB

    this row throws a error:
    ribbon.ActivateTab ("customTab"):

    Run-time error(91)
    Object variable or With block variable not set.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Copyright © 2022-present Excelguru Consulting Inc. All rights reserved.