Popup in customized ribbon

M

misi01

New member
Joined
Jul 13, 2011
Messages
31
Reaction score
0
Points
0
Location
Stockholm,Sweden
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:
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?
 
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 ?
 
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
 
You must log in or register to reply here.
Back
Top