Announcement

Collapse
No announcement yet.

Custom UI Ribbon - favorites - Dropdown

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Custom UI Ribbon - favorites - Dropdown

    Handy worksheet navigation dropdown box for those annoying multi-worksheet workbooks

    Ribbon Code
    PHP Code:
    <!--RibbonX Visual Designer 2.31 for Microsoft Excel CustomUI14 XML Code produced on 2016/11/25-->
    <
    customUI  Onload="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui" >
        <
    ribbon >
            <
    tabs >
                <
    tab 
                    id
    ="Tab1"
                    
    insertBeforeMso="TabHome"
                    
    label="My Menu">
                    <
    group 
                                  id
    ="grpDropDowns"
                        
    label="Worksheet Navigation">
                        <
    dropDown 
                            id
    ="SheetNavigation"
                            
    label="Navigate to:"
                            
    sizeString="WWWWWWWWWW"
                            
    supertip="Go to Worksheet Selected in Dropdown "
                            
    getItemCount="getItemCount"
                            
    getItemLabel="getItemLabel" 
                            
    getSelectedItemIndex="GetSelectedItemIndexDropDown"
                            
    onAction="onAction"/>
                    </
    group >
                </
    tab >
            </
    tabs >
        </
    ribbon >
    </
    customUI 

    In module vba
    Code:
    Option Explicit
    Dim Rib As IRibbonUI
    Private mwkbNavigation As Workbook
    Sub getItemCount(control As IRibbonControl, ByRef returnedVal)
        Dim lCount As Long
        Dim wksSheet As Worksheet
        Set mwkbNavigation = ThisWorkbook
        For Each wksSheet In mwkbNavigation.Worksheets
            If wksSheet.visible = xlSheetVisible Then
                lCount = lCount + 1
            End If
        Next wksSheet
        returnedVal = lCount
    End Sub
    Sub GetSelectedItemIndexDropDown(control As IRibbonControl, ByRef index)
      index = ActiveSheet.index - 1
    End Sub
    Sub getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
        If mwkbNavigation.Worksheets(index + 1).visible = xlSheetVisible Then
            returnedVal = mwkbNavigation.Worksheets(index + 1).Name
        End If
    End Sub
    Sub onAction(control As IRibbonControl, id As String, index As Integer)
        Dim sSheetName As String
        sSheetName = mwkbNavigation.Worksheets(index + 1).Name
        mwkbNavigation.Worksheets(sSheetName).Activate
    End Sub
    Sub RibbonOnLoad(ribbon As IRibbonUI)
        Set Rib = ribbon
    End Sub
    Last edited by pike; June 11th, 2017, 19:52. Reason: add Onload="RibbonOnLoad
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

  • #2
    Re: Custom UI Ribbon - favorites

    Thanks for this, very neat.
    We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

    Comment


    • #3
      Re: Custom UI Ribbon - favorites - Dropdown

      Handy when your come across workbooks with ten or more worksheets
      If the solution helped please donate to RSPCA

      Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

      Comment


      • #4
        Re: Custom UI Ribbon - favorites - Dropdown

        This is a very useful - Thanks Pike

        I need this as an AddIn which was saved as such
        Problems are as an AddIn:


        1. Dropdown content doesn't update when workbooks are opened and closed
        2. Will throw errors when sheets are not found that were populated in the dropdown from prior sheets or initial OnOpen
        3. Last, I'm looking for a way to add a scrollbar when a large number of sheets exceeds the page size - dropdown extends below the bottom of the screen


        Any help would be much appreciated

        Comment


        • #5
          Re: Custom UI Ribbon - favorites - Dropdown

          Not to sure how to fixit until the workbook changes are saved and reopened.
          With that many worksheets and combobox would be better!
          If the solution helped please donate to RSPCA

          Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

          Comment


          • #6
            Re: Custom UI Ribbon - favorites - Dropdown

            For dynamic combobox
            PHP Code:
            <!--RibbonX Visual Designer 2.31 for Microsoft Excel CustomUI14 XML Code produced on 2017/06/11-->
            <
            customUI onLoad="RibbonOnLoad"
              
            xmlns="http://schemas.microsoft.com/office/2009/07/customui" >
                <
            ribbon >
                    <
            tabs >
                        <
            tab 
                            id
            ="Tab1"
                            
            insertBeforeMso="TabHome"
                            
            label="RDS Design Manual Menu">
                            <
            group 
                                id
            ="grpDropDowns"
                                
            label="Worksheet Navigation">
                                <
            comboBox 
                                    id
            ="Combobox1"
                                    
            label="goto"
                                    
            getItemCount="Combobox1_getItemCount"
                                    
            getItemID="Combobox1_getItemID"
                                    
            getItemLabel="Combobox1_getItemLabel"
                                    
            onChange="Combobox1_onChange"/>
                            </
            group >
                        </
            tab >
                    </
            tabs >
                </
            ribbon >
            </
            customUI 
            module code
            Code:
            Option Explicit
            Dim Rib As IRibbonUI
            Dim mwkbNavigation As Workbook
            Sub RibbonOnLoad(ribbon As IRibbonUI)
                Set Rib = ribbon
            End Sub
            Public Sub Combobox1_getItemCount(control As IRibbonControl, ByRef returnedVal)
                Dim lCount As Long
                Dim wksSheet As Worksheet
                Set mwkbNavigation = ThisWorkbook
                For Each wksSheet In mwkbNavigation.Worksheets
                    If wksSheet.visible = xlSheetVisible Then
                        lCount = lCount + 1
                    End If
                Next wksSheet
                returnedVal = lCount
            End Sub
            Public Sub Combobox1_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
                If mwkbNavigation.Worksheets(index + 1).visible = xlSheetVisible Then
                    returnedVal = mwkbNavigation.Worksheets(index + 1).Name
                End If
            End Sub
            Public Sub Combobox1_onChange(control As IRibbonControl, Text As String)
                Worksheets(Text).Activate
                RefreshAddInsRibbon
            End Sub
            Public Sub RefreshAddInsRibbon()
                If Rib Is Nothing Then Exit Sub
                Rib.InvalidateControl ("Combobox1")
                DoEvents
            End Sub
            
            Public Sub Combobox1_getItemID(control As IRibbonControl, index As Integer, ByRef id)
            '
            ' Code for getItemID callback. Ribbon control comboBox
            '
            
            End Sub
            workbook module
            Code:
            Option Explicit
            Private Sub Workbook_NewSheet(ByVal Sh As Object)
                RefreshAddInsRibbon
            End Sub
            Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
                RefreshAddInsRibbon
            End Sub
            Private Sub Workbook_SheetActivate(ByVal Sh As Object)
                RefreshAddInsRibbon
            End Sub
            Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
                RefreshAddInsRibbon
            End Sub
            If the solution helped please donate to RSPCA

            Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

            Comment

            Working...
            X