Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Custom UI Ribbon - favorites - Dropdown

  1. #1
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,724

    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
    VB:
    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 at 19:52. Reason: add Onload="RibbonOnLoad

  2. #2
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,192

    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

  3. #3
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,724

    Re: Custom UI Ribbon - favorites - Dropdown

    Handy when your come across workbooks with ten or more worksheets

  4. #4
    Join Date
    27th April 2017
    Location
    Seattle Area - west sound
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,724

    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!

  6. #6
    Join Date
    9th April 2007
    Location
    Alstonville, Straya
    Posts
    5,724

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Adding a DropDown with XML to a custom ribbon
    By bfreescott in forum Excel General
    Replies: 3
    Last Post: August 12th, 2015, 03:22
  2. Controlling custom ribbon with macros
    By ryancgarrett in forum Excel General
    Replies: 1
    Last Post: July 3rd, 2013, 16:45
  3. Replies: 1
    Last Post: March 23rd, 2012, 01:39
  4. Covert 03 Custom Toolbars To 07 Ribbon
    By geo in forum HIRE HELP
    Replies: 3
    Last Post: January 22nd, 2010, 09:14
  5. Hide 2007 Ribbon & Show Custom Ribbon
    By xgf0fzp in forum Excel General
    Replies: 3
    Last Post: June 30th, 2009, 04:40

Bookmarks

Posting Permissions

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