Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Custom UI Ribbon - favorites - Dropdown

  1. #1
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    5,249

    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  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; November 27th, 2016 at 18:05.

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

    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, Australia
    Posts
    5,249

    Re: Custom UI Ribbon - favorites - Dropdown

    Handy when your come across workbooks with ten or more worksheets

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