Results 1 to 9 of 9

Thread: Trouble using getKeytip in Excel 2010

  1. #1

    Trouble using getKeytip in Excel 2010



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have an xlam AddIn which creates a custom tab in Excel. I want to allow users to choose their own keytip for this tab using the getKeytip functionality of Ribbbon XML, e.g.

    HTML Code:
    XML: <tab id="rbnTestTab" label="Test Tab" getKeytip = "getKeytip">
    Code:
    VBA: Public Sub GetKeytip(control As IRibbonControl, ByRef label)
    'get keytip function sets the keytip of a control according to its ID
    'take the value stored on the worksheet
    label = CStr(wksKeyTip.Range("rngKeytip").Value)
    End Sub
    I have a macro which gets the user's value from an InputBox, pastes it into a named range on a sheet, and then uses the .Invalidate and .InvalidateControl on the Ribbon object and the control "rbnTestTab" respectively, therefore calling the GetKeytip for the tab and setting the keytip.

    This code works fine in Excel 2007, but isn't working at all in Excel 2010, does anyone have any suggestions why not? I can't figure it out and there isn't anything in the usual useful places (Ron de Bruin's site, Ken Puls' blog, Msdn) mentioning any compatibility issues between Ribbon XML in 2007-2010.

    Two additional notes which may help (or further confuse!):
    1. The VBA callback is different between 2007-2010 (ByRef argument has different name); changing the argument to be called "returnedVal" stops the code working in 2007, but doesn't make it work in 2010, which is odd!:

    Code:
    VBA: Sub GetKeytip (control As IRibbonControl, ByRef label)
    '2007, http://msdn.microsoft.com/en-us/library/aa722523(v=office.12).aspx
    VBA: Sub GetKeytip(control As IRibbonControl, ByRef returnedVal)
    '2010, http://msdn.microsoft.com/en-us/library/ee691833.aspx
    2. If you use this code and try to set a keytip which is already assigned, e.g. "H" (Home tab), then the getKeytip fails and you get "Y" or "Y1" as the keytip on your tab, and this then cannot be changed to anything else after that by rerunning the macro/invalidate routines - you have to restart the workbook.

    Any help on how this works between 2007 and 2010 greatly appreciated! I also posted the question here: http://www.mrexcel.com/forum/showthread.php?t=544262
    Last edited by Will_R; 2011-04-18 at 04:15 PM. Reason: Added cross posting link

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14
    Hi Will, and welcome to the forum.

    Here's what I did to make this work. You can strip the pieces away to see what fails it...

    I adjusted the code to use conditional compilation, testing if the file is opening in Excel 2010 and reacting to the signature change accordingly:
    Code:
    #If VBA7 Then
        Public Sub GetKeytip(control As IRibbonControl, ByRef returnedVal)
        'get keytip function sets the keytip of a control according to its ID
        'take the value stored on the worksheet
        returnedVal = CStr(wksKeyTip.Range("rngKeytip").Value)
        End Sub
    #Else
        Public Sub GetKeytip(control As IRibbonControl, ByRef label)
        'get keytip function sets the keytip of a control according to its ID
        'take the value stored on the worksheet
        label = CStr(wksKeyTip.Range("rngKeytip").Value)
        End Sub
    #End If
    I also opened the file in the most recent version of the CustomUI Editor, and it has a section to put in Office 2010 vs Office 2007 code. So doing this I ended up with two CustomXML parts:

    The 2007 part is CustomUI.xml:
    Code:
    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
         <ribbon>
             <tabs>
                 <tab id="rbnTestTab" label="Test Tab" getKeytip = "getKeytip">
                 </tab>
             </tabs>
         </ribbon>
     </customUI>
    The 2010 part is CustomUI14.xml:
    Code:
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
         <ribbon>
             <tabs>
                 <tab id="rbnTestTab" label="Test Tab" getKeytip = "getKeytip">
                 </tab>
             </tabs>
         </ribbon>
     </customUI>
    Notice that the parts are the same except for the year/month in the xmlns.

    At this point it seems to work in both 2007 and 2010 without error. I've uploaded a sample to the post that shows this.

    PS: Can you do me a favour, just post a link at Mr Excel to let them know that the thread has been answered here. We don't want anyone over there wasting time on something that's being worked on elsewhere. Thanks!
    Attached Files Attached Files
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14
    Actually, just for grins, I decided to delete the second XML part. (So only the original CustomUI.xml remains in the file.) It seems to run just fine. So it must have been the change in ribbon signature that was messing you up. That really sucks... I'm surprised that Microsoft did that.
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    that's helpful , thank you !

  5. #5
    Seeker Mr Axolotl's Avatar
    Join Date
    Apr 2012
    Location
    Central Europe
    Posts
    7
    Articles
    0

    A similar problem with keytips

    Hello,

    I've had a similar and much more basic problem recently with keytips in Excel.

    My XML is very simple, the only complication here is a custom icon and that itself works fine.

    Code:
    <?xml version="1.0" encoding="utf-8"?>
    
    <customUI onLoad="Ribbon_Load" xmlns="htpp://schemas.microsoft.com/office/2006/01/customui">
     
    <ribbon> 
        <tabs> 
          <tab id="D2Tab" keytip="D2" label="D2" > 
            <group id="SimpleControls" label="Save To D2 As New">
              <button id="ButtonD2" image="D2Large" size="large" 
                label="Save to D2..." 
                screentip="Save a new Excel workbook to D2"
                keytip="D2"
                onAction="save2d2_click" /> 
            </group>  
          </tab>
        </tabs>
      </ribbon>
    </customUI>
    The problem is simply that the keytip for the tab always shows up as "Y", an Excel-generated keytip. There is nothing there to conflict with "D2", and I have tried other things which were even less likely to conflict, namely "KAT" and "RAT" etc. and still get the "Y". The nearly-identical XML for Word works as expected. Interestingly, the second "D2" makes it.

    I've tried changing the date in the xmlns= to ...2009/07... as suggested in another message, together with renaming the customUI.xml file to customUI14.xml. (I did not rename the subdirectory) Then no D2 label appears at all.

    Thanks very much for suggestions.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14
    D2 seems to be reserved for some reason. I can make KAT work...

    FYI, in your code above, your namespace refers to htpp instead of http...

    Code:
    <?xml version="1.0" encoding="utf-8"?> 
    <customUI 
     onLoad="Ribbon_Load"
     xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 
     <ribbon> 
      <tabs> 
       <tab id="D2Tab" 
        keytip="KAT" 
        label="D2" > 
        <group id="SimpleControls" 
         label="Save To D2 As New"> 
         <button id="ButtonD2" 
          image="D2Large" 
          size="large" 
          label="Save to D2..." 
          screentip="Save a new Excel workbook to D2" 
          keytip="D2" 
          onAction="save2d2_click" /> 
        </group> 
       </tab> 
      </tabs> 
     </ribbon> 
    </customUI>
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Seeker Mr Axolotl's Avatar
    Join Date
    Apr 2012
    Location
    Central Europe
    Posts
    7
    Articles
    0

    Thanks

    Thanks for the answer. I'll try 2009/07/customui again with different text in the keytip.

    As for htpp, this forum disallows links for the first 5 posts. As htpp://... is not a valid link, it goes undetected.

  8. #8
    Acolyte Andy Pope's Avatar
    Join Date
    Mar 2011
    Location
    London, England
    Posts
    23
    Articles
    0
    I don't think this is as simple as the callback signature, I used anyoldname and it still worked with valid text.

    This works for me in both '07 and '10, if I use valid text for the keytip. The file is now a simple .xlsm so it is easier to unload and test. The tab includes a edit box to enter various text and a button to apply that text.

    In '07 I can enter the following text and apply without issue,
    J
    Z
    KAT

    Enter H and the text displayed will be H1, Home get set to H2, and now I can not invalidate the ribbon to replace text.

    In '10 I can enter the following text and apply without issue,
    J
    KAT

    Enter H or Z and the text displayed will be Y1 or Y2, depending on existing tabs, and now I can not invalidate the ribbon to replace text.

    So it seems the way duplicate key tip text is handle is the bit that varies. '07 added index counter to letter. '10 using new text and index.
    And either version is in effected locked once a system applied text is used.

    Code:
    Public grbxUI As IRibbonUI
    Public g_vntKeyTip As Variant
    Public Sub Editbox1_onChange(control As IRibbonControl, Text As String)
    '
    ' Code for onChange callback. Ribbon control editBox
    '
    ' Max len keytip=3
        g_vntKeyTip = Trim(Left(Text & Space(3), 3))
    
    End Sub
    
    Public Sub callback_getKeytip(control As IRibbonControl, ByRef anyoldname)
    'get keytip function sets the keytip of a control according to its ID
    'take the value stored on the worksheet
    
    anyoldname = g_vntKeyTip
    Debug.Print Now(), anyoldname
    
    End Sub
    Public Sub Button1_onAction(control As IRibbonControl)
    '
    ' Code for onAction callback. Ribbon control button
    '
        grbxUI.Invalidate
        
    End Sub
    Public Sub rbx_LoadRibbon(ribbon As IRibbonUI)
    '
    ' Code for onLoad callback. Ribbon control customUI
    '
        Set grbxUI = ribbon
    End Sub
    Attached Files Attached Files
    Cheers
    Andy

    www.andypope.info

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Nothing like consistency, eh?
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •