Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Custom UI Ribbon - favorites - Dropdown

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

    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,630

    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,671

    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


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