Consulting

Results 1 to 17 of 17

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

  1. #1
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location

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

    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?
    Attached Images Attached Images
    Last edited by dschmitt; 01-13-2016 at 01:59 AM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    Last edited by Paul_Hossler; 01-13-2016 at 09:02 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    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.
    Attached Images Attached Images

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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"/>
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    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" />
    Last edited by dschmitt; 01-13-2016 at 11:32 PM.

  6. #6
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    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.
    Attached Images Attached Images
    Attached Files Attached Files

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Windows 10 Pro, Excel 2013 Professional Plus, the separators show.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    Mac OSX, Parallels Desktop, Windows 8.1, Excel 2013, the separators show.

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    Last edited by Paul_Hossler; 01-14-2016 at 07:25 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    I set the Office_Theme to "Dark Grey". With that setting the separators are still not visible.
    Attached Images Attached Images

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    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.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by dschmitt; 01-17-2016 at 09:13 PM.

  13. #13
    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.

  14. #14
    VBAX Regular
    Joined
    May 2010
    Posts
    65
    Location
    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

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    @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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    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
    Last edited by avitzavi; 02-02-2017 at 03:28 PM. Reason: code cleanup

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    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!
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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