Results 1 to 4 of 4

Thread: Popup in customized ribbon

  1. #1
    Acolyte misi01's Avatar
    Join Date
    Jul 2011
    Location
    Stockholm,Sweden
    Posts
    31
    Articles
    0

    Popup in customized ribbon



    Register for a FREE account, and/
    or Log in to avoid these ads!

    To the moderator - please change title if you think it's misleading or can be improved. To my problem.

    I have existing code (written for Office 2003) that creates a customized menu with the following (approx) options.

    Code:
    Update data
    Clear data
    ------------
    Change language ---> Swedish         (the ---> represents a popup)
                         Norwegian
                         etc etc
    ------------
    Version
    I have created this using the following code
    Code:
        ' Add another menu that will lead off to another menu (popup)
        Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
        MenuItem.Caption = menu_texts(3)        ' Change language
        MenuItem.BeginGroup = True          ' Insert a separator ABOVE this option
            
        ' Add the menu options to the sub menu, just created above
        '
        ' Make sure we point at the English flag on the technical sheet
        '
        Set sh = Sheets(technical_sheet_name)   'Change here to the worksheet for shapes
        Set shp = sh.Shapes("English")          ' Select the English flag
        shp.CopyPicture Format:=xlBitmap
        
        With MenuItem.Controls.Add(Type:=msoControlButton)
            .Caption = menu_texts(4)            ' English
            .OnAction = "change_language_english"
            .Tag = "ENGLISH"
            .PasteFace
        End With
        '
        ' Make sure we point at the Swedish flag on the technical sheet
        '
        Set shp = sh.Shapes("Swedish")          ' Select the Swedish flag
        shp.CopyPicture Format:=xlBitmap
        
        With MenuItem.Controls.Add(Type:=msoControlButton)
            .Caption = menu_texts(5)            ' Svenska
            .OnAction = "change_language_swedish"
            .Tag = "SWEDISH"
            .PasteFace
        End With
    
    etc etc
    Is this possible with ribbon coding (we are using Office 2007), and if so, can anyone point me to an example of how to do this ?
    Last edited by misi01; 2012-12-27 at 01:47 PM.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Yes, you can certainly do this in Office 2007+ using the Ribbon. Interestingly enough, my co-author for the RibbonX book wrote up a way to do this, switching between two languages and storing the pictures in an Access database, and loading them as needed. If you own our book, you can find that example on pages 269-273. (If you don't own it, the first link on this page will take you directly to our book's page on Amazon.)

    If you're not going to use an Access database, then what you need to do is:
    • Store your images in the file. I'd suggest loading them in using the CustomUI Editor
    • Create a dynamicMenu or a Gallery element
    • Load your pictures in
    • Craft the callbacks to react appropriately


    How much RibbonX programming have you done so far?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte misi01's Avatar
    Join Date
    Jul 2011
    Location
    Stockholm,Sweden
    Posts
    31
    Articles
    0

    Thank you Ken.

    Followed the link and I believe I do have the book at home (buried in a box somewhere while the place is re-decorated - naturally).

    After I'd appended I continued experimenting and found (can't now of course) something from your blog where you defined Private Function AddButtonXML which created the XML code required so that seems
    to be looking good. I've done some RibbonX programming (on a scale of 1-10, I'd consider myself a 4-5). Trouble is, I don't work with it enough to become prolific - I have to look up things a lot rather than just writing the code automatically.

    One follow-on question. In the old code, I toggled the checkmark for the selected item with the following code
    Code:
    Sub ToggleFormula(default_language)
    
    'This routine is intended to toggle the checkmark on the sub menu "Formular Bar" when the button is clicked
    Dim TG As CommandBarButton
    
    On Error Resume Next
    
    ' Start by removing the menu marks for ALL menu options - note that the values ENGLISH etc are
    ' those set as TAGS in Addmenus
    Set TG = CommandBars.FindControl(msoControlButton, , "ENGLISH")
    TG.State = msoButtonUp
    Set TG = CommandBars.FindControl(msoControlButton, , "SWEDISH")
    TG.State = msoButtonUp
    Set TG = CommandBars.FindControl(msoControlButton, , "FINNISH")
    TG.State = msoButtonUp
    
    ' Now to set the CORRECT menu option as marked
    Set TG = CommandBars.FindControl(msoControlButton, , default_language)
    
    If Application.DisplayFormulaBar Then
      TG.State = msoButtonDown      ' Indicate the menu option as selected
    End If
    
    End Sub
    How would I do the equivalent with RibbonX programming ?

    BTW - are you aware (for me using FF 17) that your signature "If you've been given VBA code ...........CLICK HERE" leads nowhere ?

  4. #4
    Acolyte misi01's Avatar
    Join Date
    Jul 2011
    Location
    Stockholm,Sweden
    Posts
    31
    Articles
    0

    Thought I'd append how far I've got

    I've managed to get it (almost) working, but there are a couple of aspects I don't understand. First my code from the UI:-
    Code:
                      <dynamicMenu id="Change_language" 
                         label="Change language" 
                         getContent="GetContent" />
    and then my VBA code
    Code:
    'Callback for Change_language getContent
    Sub GetContent(control As IRibbonControl, ByRef returnedval)
    
    Dim sXML As String, n1 As String
    Dim temp As String, menu_options As String, menu_texts(10) As String
    Dim i As Integer
    Dim lngCnt As Long, shp As Shape, sh As Worksheet
    
    '
      ' Get all the menu option texts based on the default/chosen  language
      '
      Debug.Print "In get_content before translate_value"
      Call Module1.translate_value(default_language, "MENU_OPTIONS", menu_options)
      If menu_options = "" Then
        MsgBox "Serious error - cannot fetch text for menus, cannot continue"
        Exit Sub
      End If
      
      Debug.Print "After translate_value"
      'MsgBox "Default language is " & default_language
    
    ' MsgBox "g_English_Button_Enabled = " & g_English_Button_Enabled
    'Set error handling
    On Error GoTo CloseTags
    
    sXML = "<" & "menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">"
    ' English flag
    temp = "<toggleButton id=""English_Button"" label=""English"" image=""Flag_en"" onAction=""change_language_english_ribbon"" getPressed=""Language_pressed""/>"
    sXML = sXML & temp
    ' Swedish flag
    temp = "<toggleButton id=""Swedish_Button"" label=""Svenska"" image=""Flag_sv"" onAction=""change_language_swedish_ribbon"" getPressed=""Language_pressed""/>"
    sXML = sXML & temp
    ' Norwegian flag
    temp = "<toggleButton id=""Norwegian_Button"" label=""Norsk"" image=""Flag_no"" onAction=""change_language_norwegian_ribbon"" getPressed=""Language_pressed""/>"
    sXML = sXML & temp
    ' Finnish flag
    temp = "<toggleButton id=""Finnish_Button"" label=""Suomi"" image=""Flag_fi"" onAction=""change_language_finnish_ribbon"" getPressed=""Language_pressed""/>"
    sXML = sXML & temp
    '
    CloseTags:
    'Close the menu string
    sXML = sXML & "<" & "/menu>"
    'Return the completed XML to the RibbonUI
    returnedval = sXML
    
    End Sub
    The onaction code
    Code:
    Sub change_language_english_ribbon(control As IRibbonControl, pressed As Boolean)
    '
    Debug.Print "Control.id = " & control.id
    'Call Module1.change_language("ENG")
    'Call Get_Pressed_Language("ENGLISH")
    MsgBox "In change_language_english_ribbon"
    
    End Sub
    and finally the Language_pressed code
    Code:
    Sub Language_pressed(control As IRibbonControl, ByRef pressed)
     ' Return the current behavior state.
     'Stop
     Debug.Print "In get_pressed"
     
     Select Case control.id
     
      Case "English_Button"
        g_English_Button_Enabled = True
        g_Swedish_Button_Enabled = False
        g_Norwegian_Button_Enabled = False
        g_Finnish_Button_Enabled = False
        pressed = g_English_Button_Enabled
        
      Case "Swedish_Button"
        g_English_Button_Enabled = False
        g_Swedish_Button_Enabled = True
        g_Norwegian_Button_Enabled = False
        g_Finnish_Button_Enabled = False
        pressed = g_Swedish_Button_Enabled
          
      Case "Norwegian_Button"
        g_English_Button_Enabled = False
        g_Swedish_Button_Enabled = False
        g_Norwegian_Button_Enabled = True
        g_Finnish_Button_Enabled = False
        pressed = g_Norwegian_Button_Enabled
          
      Case "Finnish_Button"
        g_English_Button_Enabled = False
        g_Swedish_Button_Enabled = False
        g_Norwegian_Button_Enabled = False
        g_Finnish_Button_Enabled = True
        pressed = g_Finnish_Button_Enabled
      
      End Select
     '
     
     'MyRibbon.InvalidateControl
     g_rbxIRibbonUI.InvalidateControl gRBX_ENGLISH_BUTTON
     g_rbxIRibbonUI.InvalidateControl gRBX_SWEDISH_BUTTON
     g_rbxIRibbonUI.InvalidateControl gRBX_NORWEGIAN_BUTTON
     g_rbxIRibbonUI.InvalidateControl gRBX_FINNISH_BUTTON
     
     Debug.Print "At end of get_pressed " & pressed
     
    End Sub
    here are a few bibs and bobs that might be relevant
    Code:
    Private Sub Workbook_Activate()
    '
    '   Copyright Handelsbanken 2012
    '
      Call Public_variables.define_public_variables
      Call Module1.hide_technical_sheet("")
      
    End Sub
    
    plus
    
    etc etc 
    Public Const gRBX_ENGLISH_BUTTON = "English_Button"
    Public g_English_Button_Enabled As Boolean
    '
    Public Const gRBX_SWEDISH_BUTTON = "Swedish_Button"
    Public g_Swedish_Button_Enabled As Boolean
    '
    Public Const gRBX_NORWEGIAN_BUTTON = "Norwegian_Button"
    Public g_Norwegian_Button_Enabled As Boolean
    '
    Public Const gRBX_FINNISH_BUTTON = "Finnish_Button"
    Public g_Finnish_Button_Enabled As Boolean
    '
    Public default_language As String
    
    
    Sub define_public_variables()
    '
    ' Set various global variables that will be initted when the user opens the workbook
    '
    
    technical_sheet_name = "Technical sheet"
    raw_data_row = 1
    sw_raw_data = 0
    first_raw_data_row = 3
    
    ' Set nr_lines_per_currency to 2 if the total currency lines should be shown as
    ' Total valid pay amount SEK
    '   18 250,00
    '
    ' otherwise set to 1 if they should be shown as
    '   Total valid pay amount SEK - 18 250,00
    '
    nr_lines_per_currency = 1
    
    g_English_Button_Enabled = False
    g_Swedish_Button_Enabled = False
    g_Norwegian_Button_Enabled = False
    g_Finnish_Button_Enabled = False
    
    ' See if we can get the data from the Technical sheet
    default_language = ""
    Call determine_default_language(default_language)
    
    
    End Sub

    Okay - here's what I don't understand. Without the pressed As Boolean in change_language_english_ribbon I was getting Wrong number of arguments or ............ By sheer trial and error, I discovered tha the missing argument was the problem. Trouble is, how do you debug ribbon code when you've got an error like that - there was nothing that indicated where the error was. Should I have switched on some debug feature I'm unaware of ?

    When I first select the Change language menu option, ALL the flags are shown as selected (whereas I would have thought they wouldn't be).

    In addition, every time I select a language, based on the code in Language_pressed I would have expected them to checked (or not) depending on the language I selected. So, selecting English should "unmark" the other 3 languages (which it isn't). Do I have to have separate subs for each language option or ..... ??

    Thank you

Posting Permissions

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