David G David G - 4 months ago 5
HTML Question

Change size of Custom Excel Ribbon dropdown

enter image description here

I have this dropdown in the ribbon containing all the visible sheets in the workbook. Users can select a sheet in there to jump to it. It's important because there are a ton of sheets in this workbook. Unfortunately when the name of the sheet is long, it doesn't show completely. I'd like to make it wider.

I used the CustomUI Editor for Microsoft Office to create it using my not-very-fluent HTML skills. Here is part of the code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="InitS3Ribbon">
<ribbon>
<tabs>
<tab id="s3Tab" label="S3 Menu">
<group id="grGeneral" label="General">

<dropDown id="navigation" label="Navigation" getItemCount="GetNavigateItemCount" getItemLabel="GetNavigateLabel" onAction="MenuNavigate" getSelectedItemIndex="SetNavigateIndex" showLabel="true" />

<button id="bShowHideSheet" imageMso="PivotPlusMinusButtonsShowHide" label="Show/Hide sheets" size="normal" onAction="MenuShowHideSheets" />
<button id="bPreviousPage" imageMso="LeftArrow2" label="Previous sheet" size="large" onAction="MenuPreviousSheet" />
<button id="bNextPage" imageMso="RightArrow2" label="Next sheet" size="large" onAction="MenuNextSheet" />
</group>


I found this resource saying that it can be changed with the SizeString property but I'm not even sure how or where to include that in my code. Looks to me like it's supposed to be in the VBA section? I'm not sure I understand and I'd like guidance. I'm not sure whether to edit HTML or VBA right now and how.

Answer

The official XML spec can be found here: https://msdn.microsoft.com/en-us/library/cc313070(v=office.12).aspx

It looks like sizeString can be used directly as an attribute to your dropdown XML tag. So something like:

<dropDown id="navigation" label="Navigation" sizeString="MY_MAX_LENGTH_STRING" getItemCount="GetNavigateItemCount" getItemLabel="GetNavigateLabel" onAction="MenuNavigate" getSelectedItemIndex="SetNavigateIndex" showLabel="true" />

You'll just have to know what the longest string you'll encounter will be, then put that in as MY_MAY_LENGTH_STRING. Considering you're using the dropdown to hold sheetnames, which are capped at 31 characters, you could probably use that length as a starting point.

Comments