How to limit the ribbon to 2 normal size icons per column

[dschmitt]

In the Excel ribbon, in one column, as far as I know, which may be wrong, one can either place one large size or 3 normal size icons.
Is there a way to limit one column to 2 icons?

I attached a screenshot that illustrates my question.
The screenshot (right image) shows a section of my ribbon. In the left column there are 3 normal size icons. And in the column next to it there is 1 normal size icon.
I would like to display in both columns 2 icons as seen in the second image which I created in PowerPoint. Is that possible? If yes, how can I do that?

[Paul_Hossler]

Use <box>

Here's some sample usage -- look at box1 and box2

BTW, I assumed that left and right pictures were reversed in your post

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomTab" label="My Tab">

<group id="CustomGroup1" label="Horizontal Boxes">

    <box id="box1" boxStyle="horizontal">
        <button id="buttonA1" label="Button1" onAction = "ClickButton" imageMso="HappyFace"/>
       <button id="buttonA2" label="Button2" onAction = "ClickButton" imageMso="HappyFace"/>
   </box>

  <box id="box2" boxStyle="horizontal">
   <button id="buttonAA" label="ButtonA" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonAB" label="ButtonB" onAction = "ClickButton" imageMso="HappyFace"/>
  </box>

  <separator id="separator2"/>

  <button id="buttonAC" label="ButtonC" size="large" onAction = "ClickButton" imageMso="HappyFace"/>

 </group>

 <group id="CustomGroup2" label="Vertical Boxes">
  <box id="box3" boxStyle="vertical">
   <button id="buttonB1" label="Button1" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonB2" label="Button2" onAction = "ClickButton" imageMso="HappyFace"/>
  </box>
  <separator id="separator3"/>
  <box id="box4" boxStyle="vertical">
   <button id="buttonBA" label="ButtonA" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonBB" label="ButtonB" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonBC" label="ButtonC" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonBD" label="ButtonA" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonBE" label="ButtonB" onAction = "ClickButton" imageMso="HappyFace"/>
   <button id="buttonBF" label="ButtonC" onAction = "ClickButton" imageMso="HappyFace"/>
  </box>
 </group>
 <group id="CustomGroup3" label="No Boxes">
  <button id="buttonC1" label="Button1" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonC2" label="Button2" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCA" label="ButtonA" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCB" label="ButtonB" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCC" label="ButtonC" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCD" label="Button1" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCE" label="Button2" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCF" label="ButtonA" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCG" label="ButtonB" onAction = "ClickButton" imageMso="HappyFace"/>
  <button id="buttonCH" label="ButtonC" onAction = "ClickButton" imageMso="HappyFace"/>
 </group>
</tab>
</tabs>
</ribbon>
</customUI>

Capture.JPG

[dschmitt]

Paul, thank you. I didn't know about Boxes.

One more question.
I am using Excel 2013. Do you know whether there is a way to make the separators visible in this Excel version?
As you can see in the screenshot below indicated by the red arrows the 2 separators visible in your Ribbon are not visible in my Ribbon.

[Paul_Hossler]

The <separator> XML doesn't have any options as far as I know. I don't have 2013 any more, just the 2016

<separator id="separator2"/>

[dschmitt]

I see. I copy/pasted your script to recreate your example ribbon. That includes the script for the 2 separators.
As you can see in my screenshot, the separators don't appear in the Ribbon. But otherwise the Ribbon script is functional.
As you know, if there would be scripting errors the Ribbon Tab would not appear. So, the script is fine. It seems Excel 2013 is ignoring the script defining the separators.

Including visibility is true doesn't make a difference.

<separator id="separator1" visible="true" />

[dschmitt]

I tried the Ribbon script in 2 different Excel/Windows versions. Below is the result.
Separators visible: yes/no

MS Office Professional Plus 2013 / Windows 8.1: no (top screenshot)
MS Office Standard 2013 / Windows 7: yes (bottom screenshot)

To try it on your computer use the attached file.

[Bob Phillips]

Windows 10 Pro, Excel 2013 Professional Plus, the separators show.

[dschmitt]

Mac OSX, Parallels Desktop, Windows 8.1, Excel 2013, the separators show.

[Paul_Hossler]

On the Windows PC, the separator might be too faint to show clearly. They are a little faint on my PC

Change the color scheme and see. This is the 2016 Dark Gray scheme



Capture.JPG

[dschmitt]

I set the Office_Theme to "Dark Grey". With that setting the separators are still not visible.

[Paul_Hossler]

I think they changed the definition of Dark Gray in 2016.

All I can suggest is that you try the other themes and/or play with your video setting (like maybe contract) to see

The line really is there, but for some reason I think it's too faint to see on your Wndows PC

[dschmitt]

Paul, I tested whether the color contrast is the problem.
I created 2 Excel files each containing a custom tab with 2 large buttons. In one of the 2 files I placed a separator between the 2 buttons. See attached images and files.
Then I opened both file on the computers where I can see and where I cannot see separators.
On the computer where I can see separators, when toggling between the 2 files, I can see that the separator is changing the distance between the 2 buttons.
On the computer where I cannot see separators the distance between the 2 buttons remains the same.
This result indicates to me that the color contrast is not the problem.

[avitzavi]

Is there a way to do this in VBA?

I have code for my addin build up from a few macros using Commandbarbuttons and they all appear as a horizontal row and I would really like it if it were stacked vertically.
I'd link you to my post but it doesnt seem to be allowing it.

[dschmitt]

avitzavi, use the the function: boxStyle="vertical". See example below.

<group id="customGroup1" label="Test" insertAfterMso="DEVELOPER">    
        <box id="box1" boxStyle="vertical">
               <button id="customButton1" label="A" size="normal" onAction="Test1"/>
               <button id="customButton2" label="B" size="normal" onAction="Test2"/>
               <button id="customButton3" label="C" size="normal" onAction="Test3"/>
       </box> 
</group>

All you need to know about Ribbon programming you can find in the link below.
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[Paul_Hossler]

@avitavi --

http://www.vbaexpress.com/forum/show...range-vertical!


I think this is the post you were referring to

You need to add some CustomUI XML to your add in to modify the ribbon including vertical formatting, and the CustomUI buttons can then call your macros

[avitzavi]

Yup! thats my thread.
Can you possibly provide an example of how CustomUI can be incorporated to my code?

Sub Auto_Open()
    Dim oToolbar As CommandBar
    Dim oButton As CommandBarButton
    Dim MyToolbar As String

    ' Give the toolbar a name
    MyToolbar = "Analyst Toolkit"

    On Error Resume Next
    ' so that it doesn't stop on the next line if the toolbar's already there

    ' Create the toolbar; PowerPoint will error if it already exists
    Set oToolbar = CommandBars.Add(Name:=MyToolbar, _
        Position:=msoBarFloating, Temporary:=True)
    If Err.Number <> 0 Then
          ' The toolbar's already there, so we have nothing to do
          Exit Sub
    End If
    'oToolbar.Width = 100
    
    On Error GoTo ErrorHandler

'Button 1
    Set oButton = oToolbar.Controls.Add(Type:=msoControlButton)
With oButton
    .DescriptionText = "Copy Size & Position"
    .Caption = "&Copy Size/Position"
    .OnAction = "CopyPositionSize"
    'Runs the Sub CopyPositionSize() code when clicked
    .Style = msoButtonIconAndWrapCaption
    .FaceId = 3985
    End With


'Button 2
    Set oButton = oToolbar.Controls.Add(Type:=msoControlButton)
    With oButton
    .DescriptionText = "Paste Size & Position"
    .Caption = "&Paste Size/Position"
    .OnAction = "PastePositionSize"
    'Runs the Sub CopyPositionSize() code when clicked
    .Style = msoButtonIconAndWrapCaption 
    .FaceId = 4157
    End With
    
'Button 3
    Set oButton = oToolbar.Controls.Add(Type:=msoControlButton)
    With oButton
    .BeginGroup = True
    .DescriptionText = "Save Copy with No Links"
    .Caption = "Export and &Break Links"
    .OnAction = "BreakAllLinks"
    'Runs the Sub CopyPositionSize() code when clicked
    .Style = msoButtonIconAndWrapCaption
    .FaceId = 2647
    End With
    
'Button 4
    Set oButton = oToolbar.Controls.Add(Type:=msoControlButton)
    With oButton
        .DescriptionText = "Save Copy with No Links"
        'Tooltip text when mouse if placed over button
        .Caption = "Export, Break Links and &Email"
        'Text if Text in Icon is chosen
         .OnAction = "BreakAllLinksAndEmail"        'Runs the Sub CopyPositionSize() code when clicked
        .Style = msoButtonIconAndWrapCaption
        .FaceId = 2986
    End With
    
oToolbar.Visible = True


NormalExit:
    Exit Sub   ' so it doesn't go on to run the errorhandler code


ErrorHandler:
     'Just in case there is an error
     MsgBox Err.Number & vbCrLf & Err.Description
     Resume NormalExit:


End Sub

[Paul_Hossler]

This is the most straight forward way to do it. There are more elegant ways

You don't use the add commandbar logic. Use the CustomUI editor ....

http://openxmldeveloper.org/blog/b/o...8/06/7293.aspx

http://openxmldeveloper.org/cfs-file...ditorSetup.zip


....to add this to your XLSM or PPTM.

Once there save it as the appropriate addin (i.e. PPAM)

You can use built in icons, add your own, or not use any. I just used ABCD because it's easy

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="cbOnRibbonLoad" >

<ribbon>
<tabs>
<!-- Add Custom group to the Home tab in the ribbon -->
<tab id="myTab" label="My Tab">
 <group id="myGroup" label="My Addin">
  <box id="box4" boxStyle="vertical">
   <button id="bCopyPosition" label="Copy Pos" onAction = "cbCopyPositionSize" imageMso="A"/>
   <button id="bPastePosition" label="Paste Pos" onAction = "cbPastePositionSize" imageMso="B"/>
   <button id="bBreakLinks" label="Break Links" onAction = "cbBreakAllLinks" imageMso="C"/>
   <button id="bBrealLinksEmail" label="Break Email" onAction = "cbBreakAllLinksAndEmail" imageMso="D"/>
  </box>
 </group>
</tab>
</tabs>
</ribbon>
</customUI>

The callbacks (i.e. what the button calls) look like these

Option Explicit
Public oRibbon As IRibbonUI

'Callback for customUI.onLoad
Sub cbOnRibbonLoad(ribbon As IRibbonUI)
    Set oRibbon = ribbon
End Sub

'Callback for bCopyPosition onAction
Sub cbCopyPositionSize(control As IRibbonControl)
    CopyPositionSize
End Sub

'Callback for bPastePosition onAction
Sub cbPastePositionSize(control As IRibbonControl)
    PastePositionSize
End Sub

'Callback for bBreakLinks onAction
Sub cbBreakAllLinks(control As IRibbonControl)
    BreakAllLinks
End Sub

'Callback for bBrealLinksEmail
Sub cbBreakAllLinksAndEmail(control As IRibbonControl)
    BreakAllLinksAndEmail
End Sub

Each button call back then calls your 'real' macro. These are just stubs

Option Explicit
Sub CopyPositionSize()
    MsgBox "CopyPositionSize"
End Sub

Sub PastePositionSize()
    MsgBox "PastePositionSize"
End Sub

Sub BreakAllLinks()
    MsgBox "BreakAllLinks"
End Sub

Sub BreakAllLinksAndEmail()
    MsgBox "BreakAllLinksAndEmail"
End Sub

Look through the threads in this forum for ideas, and there are many 'how to' references on the web


Capture.JPG


This is for Excel because that's what I had handy (the process is the same), but John was also kind enough to add more references and a PP addin example to the original thread


http://www.vbaexpress.com/forum/show...range-vertical!