PDA

View Full Version : Trouble using getKeytip in Excel 2010



Will_R
2011-04-18, 05:14 PM
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.


XML: <tab id="rbnTestTab" label="Test Tab" getKeytip = "getKeytip">

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!:


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

Ken Puls
2011-04-18, 11:07 PM
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:

#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:

<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:

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

Ken Puls
2011-04-18, 11:11 PM
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.

Jessica Kloss
2011-11-24, 10:18 AM
that's helpful , thank you !

Mr Axolotl
2012-04-04, 07:48 PM
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.


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

Ken Puls
2012-04-05, 10:18 PM
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...


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

Mr Axolotl
2012-04-09, 12:21 PM
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.

Andy Pope
2012-04-10, 05:25 PM
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.



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

Ken Puls
2012-04-10, 08:28 PM
Nothing like consistency, eh? :)