none
Show Custom Ribbon Tab at Workbook Open RRS feed

  • Question

  • Using Office 2016 and Windows 10. 

    I am trying to make a custom ribbon tab show when a particular Excel workbook is opened as the first instance of Excel.  I can make this happen when I step through my procedures using F8 (after inserting "Stop" into the code).  But when the workbook is opened without the Stop, the custom ribbon tab is not shown, and the ribbon remains on the "Home" tab.

    The relevant line of XML from the CustomUI Editor is

    <customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">

    And the relevant code in a normal module is:

    Option Explicit
    Public ribRibbon As IRibbonUI
    
    'Callback for customUI,onLoad
    Public Sub RibbonOnLoad(ribbon As IRibbonUI)
        Set ribRibbon = ribbon
        ribRibbon.ActivateTab ("tabColtar")
    End Sub

    I get no errors;  it just does not show the Coltar tab.

    However, when I insert Stop into the code above Set ribRibbon=ribbon, and save and reopen the workbook, the code duly stops at the Stop point and shows the VBE.  I can then step through with F8 and the Coltar ribbon tab is shown.  (I note, however, that it is not shown until the End Sub command has been completed - I expected it after the ribRibbon.ActivateTab ("tabColtar") command.)

    I have tried introducing pauses of up to 5 seconds with the Application.Wait command, putting the command in various places, including the Workbook Open event.  I have also tried using the Public Sub to call another Sub to show the tab.  Again, that works when I use Stop and F8, but not when the workbook opens normally.

    Now a further complication:  if, when I open the workbook, there is already another workbook open, then the procedure works correctly and the Coltar tab is shown as required.  But when the workbook with the ribbon tab is the first instance of Excel, it does not work.

    Edit Addition:  Retrying the procedure a few hours later, the Custom Ribbon Tab no longer opens even if another workbook is open.  As far as I can tell, nothing has changed, and the tab still shows if I use F8 to  step through after putting Stop into the procedure.

    I had previously been using Sendkeys to show the Coltar tab, but although this works when opening the workbook by a double click, when the workbook is opened from a procedure in a different workbook, this does not work.  The keys to select the tab are Alt+Y1, which in code equates to %Y1%, and the second item (i.e. "1") is instead inserted into the active cell in the active workbook.  I have no idea why!  Hence my wish to use the XML / VBA option to select the tab.

    Any guidance on how to make this work in all situations would be much appreciated.  I suppose I could use both methods, with a condition on the Sendkeys option to ensure there is not another workbook open, but that is hardly an elegant solution!

    Andy C


    Wednesday, November 2, 2016 12:11 PM

Answers

  • The answer to this question is embarrassingly simple.  One simply makes the custom tab the first one on the ribbon by changing the XML <tab> line to:

    <tab id="tabColtar" label="Coltar" insertBeforeMso="TabHome">

    Unfortunately it is difficult to get a full definition of all the elements in the schema, so I had not realised that the "InsertBeforeMso" term existed.

    Andy C

    • Marked as answer by AndyColRomsey Friday, March 3, 2017 12:08 PM
    Thursday, March 2, 2017 11:24 AM

All replies

  • Hi,

    According to your description, my steps:

    1)create a .xlsm file, select "Show add-in user interface errors" in the Excel Options

    2)insert macro in the normal module

    Option Explicit
    Public ribRibbon As IRibbonUI
     
    'Callback for customUI,onLoad
    Public Sub RibbonOnLoad(ribbon As IRibbonUI)
        Set ribRibbon = ribbon
        ribRibbon.ActivateTab ("tabColtar")
    End Sub

    3)open the file in CustomUI Editor

    4)Insert XML and save

    <customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
    <tabs>
    <tab id="tabColtar" label="Contoso" insertAfterMso="TabHome">
    <group id="customGroup" label="Contoso Tools">
    <button id="customButton1" label="ConBold" size="large" imageMso="Bold" />
    <button id="customButton2" label="ConItalic" size="large" imageMso="Italic" />
    <button id="customButton3" label="ConUnderline" size="large" imageMso="Underline" />
    </group>
    </tab>
    </tabs>
    </ribbon>
    </customUI>

    Result: Open the file, the custom tab is activated

     

    I am using Office 16.0.7329.1017 32bit/ Win10.

    Since we failed to reproduce your issue, I suggest you test the steps above and check if it works.

    Make sure "Show add-in user interface errors" in the Excel Options selected. 

    If possible, I suggest you share your whole XML or the file with us.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 4, 2016 7:34 AM
    Moderator
  • Celeste

    Many thanks for looking at this.  I ran your code as suggested in a new macro enabled workbook and it worked just fine, as indeed did a very basic workbook that I had created when developing the code.  I have ticked "Show add-in user interface errors" in Options too.

    Having done that I re-entered all the code in my first post into the workbook giving me the problem, saved, closed and opened it and it worked twice.  Third and all subsequent tries it just does nothing - simply opens on the Home ribbon tab whether opened by double click or by putting "Workbooks.Open("C:\ ... etc") into the immediate window.  I have received no  error messages.  I have tried all the following:

    1.  Commented out all the "This workbook - Workbook Open" event procedures.

    2.  Set Application.EnableEvents to false.

    3.  Put Stop as the first line of the Public Sub RibbonOnLoad(ribbon As IRibbonUI) procedure.  In this case it duly stops with the VBE showing and using F5 or F8 makes the procedure work and show the ribbon custom tab as I expect.

    4.  Closed down and restarted my computer.

    5.  Finally, and very frustratingly, I have included exactly the same VBA and XML code elements as shown in my first post into another large workbook in the same suite as the one giving the problem, and it works perfectly every time.  I.e. when that workbook opens, the "Coltar" ribbon Tab is showing.

    I am using Office 16.0.7167.2040 from Office 365 and it is automatically updated.

    The problem workbook has something over 1000 lines of code, so I can't post it all.  The  XML is below, and CustomUI shows that it is well formed.

    <customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    	<ribbon startFromScratch="false">
    		<tabs>
    			<tab id="tabColtar" label="Coltar" insertAfterMso="TabHome">				
    				<group id="grpActions" label="Actions">
    					<button id="GatherMerge" label="Merge Payments" imageMso="MergeCellsAcross" size="large" screentip="Merges Paid Items" supertip="Checks the validity of the selected group, sums the payments and merges the cells into one payment group, showing all reference numbers" onAction="CallGatherToMerge" />
    				</group>
    				<group id="grpNavigation" label="Navigation">
    					<button id="btnSTSales" label="£ Load Sale" imageMso="SelectRow" size="normal" screentip="Shows final £ Sale" supertip="Displays the area of the bottom row in the Sales Credits section." onAction="CallST_SalesCredits" />
    					<button id="btnSTPurchase" label="£ Load Purchase" imageMso="SelectRow" size="normal" screentip="Shows final £ Purchase" supertip="Displays the area of the bottom row in the Purchase Debits / Loads section." onAction="CallST_Loads" />
    					<button id="btnEUSales" label="€ Load Sale" imageMso="SelectRow" size="normal" screentip="Shows final € Purchase" supertip="Displays the area of the bottom row in the Euro Sales Credits section." onAction="CallEU_SalesCredits" />
    					<button id="btnSTHauliers" label="£ Hauliers" image="Haulier" size="normal" screentip="Shows final £ Haulier" supertip="Displays the area of the bottom row in the Sterling Hauliers section." onAction="CallST_Hauliers" />
    					<button id="btnEUHauliers" label="€ Hauliers" image="Haulier" size="normal" screentip="Shows final € Haulier" supertip="Displays the area of the bottom row in the Euro Hauliers section." onAction="CallEU_Hauliers" />
    				</group>
    				<group id="grpFormatting" label="Formatting">
    					<button id="btnFuchsia" label="Magenta Fill" imageMso="ColorFuchsia" size="normal" screentip="Magenta Cell Fill" onAction="CallCellMagenta" />
    					<button id="btnGreen" label="Green Fill" imageMso="ColorLime" size="normal" screentip="Green Cell Fill" onAction="CallCellGreen" />
    					<button id="btnNoFill" label="No Fill" imageMso="ColorWhite" size="normal" screentip="No Cell Fill" onAction="CallCellNoFill" />
    				</group>
    				<group id="grpSuperUser" label="Super User Actions">
    					<button id="HideProtect" label="Hide and Protect" imageMso="ReviewProtectWorkbook" size="large" screentip="Toggles between hidden and visible for sensitive rows and for Cash Flow tab" supertip="When all rows and the Cash Flow tab are visible, hides sensitive rows in All Assets tab and hides Cash Flow tab.  Hardcoded password is set automatically.  Change of password requires rewriting of the code."  onAction="callHideUnhide" />
    					<button id="EndOfMonth" label="End of Month" imageMso="CopyOrMoveToSection" size="normal" screentip="Clear Previous Month Data" supertip="Use with coution;  this action cannot be reversed.  Find all transactions relating solely to previous months and move them to a new workbook that is then saved.  Current and future month transactions are preserved and the column for the previous month is deleted." onAction="CallClearLastMonthData" />					
    				</group>
    			</tab>
    		</tabs>
    	</ribbon>
    </customUI>
    

    I have now tried putting the same code elements into the same file on another computer using Windows 10 and Office 2016 and I get the same symptoms.  First two times of opening the file it opens correctly on the Coltar ribbon tab, thereafter it opens on the Home  tab.

    As a further complication I have found that if I open Excel from the Start menu with no workbook opened and I then click on the file from the "Recent Files" list, it now opens as required with the "Coltar" ribbon tab showing.  This happens every time.  Still when I double click the file from File Explorer, it does not open on the Coltar tab.

    I realise that all this information seems contradictory, but if you have any ideas on other avenues that I can explore in diagnosing this I would be very grateful.

    Andy C

    Friday, November 4, 2016 2:12 PM
  • Hi,

    I test the updated XML, removing all the onAction callback, it still works.

    According to your description, only the specific file doesn’t work normally.

    I suggest you share us the file, otherwise it is hard for us to find the cause.

     

    >>I have included exactly the same VBA and XML code elements as shown in my first post into another large workbook in the same suite as the one giving the problem, and it works perfectly every time

     

    If you have the same file which works fine, I suggest you compare the two files by using Open XML SDK Tool to check the differences.

    You could download it from https://www.microsoft.com/en-us/download/details.aspx?id=30425


    >>As a further complication I have found that if I open Excel from the Start menu with no workbook opened and I then click on the file from the "Recent Files" list, it now opens as required with the "Coltar" ribbon tab showing.  This happens every time.  Still when I double click the file from File Explorer, it does not open on the Coltar tab.

     

    If it works when opening from Backstage and does not when directly opened, do you install several version of Office in your PCs? If so, the file might be opened by using different version.

     

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 7, 2016 10:00 AM
    Moderator
  • Hi Celeste

    Thanks for your reply.  I have put the file into Dropbox at https://dl.dropboxusercontent.com/u/51949344/Financial.xlsm where I hope that you will be able to access it.  There are a number of workbook open events, so when I opened it from the link several VBA errors showed, but all the code is there.

    Edit:  After submitting this reply and clicking the link to check that it works I find that if I open the file directly from the link rather than saving, it opens on the Custom ribbon tab as intended.  If I save it and double click to open it, it does not open on the required tab.  I have no idea what is going on!

    I should clarify that when I said that I had included the same code elements into another workbook, it was a different file entirely.  It simply uses the same code elements to open on another custom ribbon tab.

    I have only one version of Excel on my computer, so when it opens successfully from the Backstage Recent Files list, it is using the same Office 2016 program.

    with thanks

    Andy C



    Monday, November 7, 2016 12:10 PM
  • Hi,

    Your file is fine. In fact, the different performance is based on if there are any other workbooks opened.

    A key point: Office Ribbon is loaded when the first workbook is opened . So if there are any other workbooks opened, the ribbon is ready. When you open this workbook which you customize the ribbon, the callback RibbonOnLoad would not fire.

    So, for your issue now, I think the improper part is activating the custom tab on the ribbon onload event since you are customizing for only one document.

    Sendkeys now is a perfect solution and I think there is no need to customize the onload event then.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 8, 2016 6:00 AM
    Moderator
  • Celeste

    Very many thanks for the time and the effort you have put into looking into this problem.

    I think there must be more to the problem than simply whether another workbook is already open.  For example the simple example xml and vba code you sent me in your first reply always opens on the "Contoso" tab whether another workbook is open or not, and my other workbooks with similar code also open on the "Coltar" tab.  So I think there is something different in this one workbook, which I will now investigate further.  Possibly it is related to the fact that I have some protection and a hidden worksheet in this workbook.

    The sendkeys option is OK if the workbook is opened from a double click, as I described in my first post on this topic.  But if it is opened from code in another workbook it fails, apparently because the Alt key to open it is "Y1".  Sendkeys seems to work when there is a single letter (e.g. "H" for the Home tab), but it seems to read the second part (i.e. the "1" of "Y1") as an input and inserts it into the active cell if a worksheet is active, or even into the VBE if I am stepping through the code with F8.

    For the moment I am afraid the users will have to do without having the "Coltar" tab showing automatically when the workbook is open.  If I  come up with an answer I will post it here.

    With thanks again

    Andy C

    Tuesday, November 8, 2016 3:17 PM
  • The answer to this question is embarrassingly simple.  One simply makes the custom tab the first one on the ribbon by changing the XML <tab> line to:

    <tab id="tabColtar" label="Coltar" insertBeforeMso="TabHome">

    Unfortunately it is difficult to get a full definition of all the elements in the schema, so I had not realised that the "InsertBeforeMso" term existed.

    Andy C

    • Marked as answer by AndyColRomsey Friday, March 3, 2017 12:08 PM
    Thursday, March 2, 2017 11:24 AM
  • For the sendkeys to work, i'm having good results including pauses.

    Here two working examples (both tested with an open workbook):

    Sub test_1()
    Dim Pausa As Long, Inicio As Long
    Pausa = 0.1
    
    'sintaxis adapted from these references:
    'http://www.vbforums.com/showthread.php?747425-SendKeys-and-Windows-8
    'http://www.vbforums.com/showthread.php?745925-RESOLVED-How-to-trigger-the-desktop-context-menu&p=4570289&viewfull=1#post4570289
    CreateObject("WScript.Shell").SendKeys "%O", True
    Inicio = Timer
    Do While Timer < Inicio + Pausa
      DoEvents
    Loop
    CreateObject("WScript.Shell").SendKeys "C", True
    
    End Sub

    And the second option:

    Sub test_2()
    Dim Pausa As Long, Inicio As Long
    Pausa = 0.1
    
    'Another alternative is using MySendKeys UDF by Karl E. Peterson (awesome I think)
    '"provides a drop-in replacement for the standard SendKeys statement, and should work just fine in all the environments (VB5, VB6 IDE, VBA other than Office 2007) where this broke"
    'http://vb.mvps.org/samples/SendInput
    'Important: read the license terms in the UDF code module
    MySendKeys "%O"
    Inicio = Timer
    Do While Timer < Inicio + Pausa
      DoEvents
    Loop
    MySendKeys "C"
    
    End Sub

    Finally for the 'show custom tab on open', i would suggest this adaptation in your code:

    Option Explicit
    Public ribRibbon As IRibbonUI
    
    'Callback for customUI,onLoad
    Public Sub RibbonOnLoad(ribbon As IRibbonUI)
        Set ribRibbon = ribbon
    
    Dim Pausa As Long, Inicio As Long
    Pausa = 0.1
    'loading is slower if there aren't other books open
    Inicio = Timer
    Do While Timer < Inicio + IIf(Workbooks.Count = 1, 5 * Pausa, Pausa)
      DoEvents
    Loop
    
    'and after this pause one of the options commented above, for example:
    MySendKeys "%Y"
    Inicio = Timer
    Do While Timer < Inicio + Pausa
      DoEvents
    Loop
    MySendKeys "Y01"
    
    End Sub


    • Edited by robertocm2 Monday, May 1, 2017 1:09 PM improved format of code
    Sunday, April 30, 2017 6:02 PM
  • Awesome ! worked just fine. I was looking for this for sometime and this works perfect. 

    Thank you for your help :)

    Friday, March 23, 2018 9:30 AM