Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Macro links on ribbon

Preparing for an update of some macros from office 2000 to office 2010
ribbonThere I have made a couple of test macros.  Both appear in the ribbon segment.

Question:  Is it possible (and if so how) to create a drop drown menu of the individual macros the same way the makros element appears and behaves in word 2010  ?
0
AndyAinscow
Asked:
AndyAinscow
  • 7
  • 6
  • 4
2 Solutions
 
DrTribosCommented:
To make a dropdown I believe you need to use XML... see this question.

It has a working example that I think you could adapt to your needs.
0
 
AndyAinscowAuthor Commented:
Thanks, I've read the comments in the link.  I'll try to look at that UI utility soon.
0
 
DrTribosCommented:
Ok . Word of warning. Make sure that your document is fully closed before you open it in the ui editor else you will lose all the xml
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AndyAinscowAuthor Commented:
It wont be a document as such, it will be document template(s) so the users can generate new documents with the custom functionality specific to that type of document.
0
 
DrTribosCommented:
Same applies! You've been warned ;-)
0
 
Chris BottomleyCommented:
I apologise if I miss the point but I presume you want a list of macros that you can execute when using 2010?

If so then enable the developer ribbon: File | Options | Customise ribbon ... in the right hand window enable the developer tab
now in the main screen you will see the developer tab, and on the code pane you will find the macros button which lists the available macros the same as in earlier versions.

Chris
0
 
AndyAinscowAuthor Commented:
>>I apologise if I miss the point

Don't, an alternative can sometime lead to solving the problem.

Anyway - nice idea, I'll consider it.  I'm just wary about such a complete list, nicer just to show the macros that the user should have easy access to.
0
 
Chris BottomleyCommented:
You can of course assure this by making the macros to which they should not have access as 'private' or adding dummy (unused parameters).  None of these would then be displayed in the box.

Chris
0
 
AndyAinscowAuthor Commented:
Neat.
0
 
DrTribosCommented:
My appologies... I thought you specifically wanted a 'drop down menu' box.... I don't know how to do that using Chris' approach but if you can live without then Chris' approach is the easiest.  A few points...

IF you plan to distribute the macros THEN you will need to put the ribbon into a template (well technically you don't need to but if you don't then distributing them would replace any customisation that the user had).
0
 
AndyAinscowAuthor Commented:
>>My appologies... I thought you specifically wanted a 'drop down menu' box....

No apologies necessary, that is what I asked for (and I will explore your suggestion in the near future).  Chris has suggested an alternative, not quite what I asked for but would do the job should modifying the UI in the way I envisaged prove to be too much of a hassle.
0
 
Chris BottomleyCommented:
You absolutely can add a drop down or combo box to the ribbon which would require the document XML as well as vba to select the affected macros ... Or if the macros are fixed then they can be placed in the XML but you would still require some vba.

If that's how you want it then of course we can help!

Chris
0
 
Chris BottomleyCommented:
AGain if I understand:

XML entered for example using customUI:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonControl.Onload">
    <ribbon>
    <?ribbon startFromScratch="true" ?> 
    <tabs>
      <tab id="CustomTab1" label="My First Tab">
        <group id="grpCombo1" label="Combo Group 1" >
		<?Comment Only bracketed by Question marks as shown?>
		<comboBox id="cbo1" 
			label="Combo Box - XML List" 
			onChange = "cboChangeHandler" 
			getText="GetComboText" 
		>
		  <item id="Macro1" label="Macro1" />
		  <item id="Macro3" label="Macro3" />
		</comboBox>
		<comboBox id="cbo2" 
			label="Combo Box - VBA List" 
			getText="GetComboText" 
			getItemCount = "ItemCountCallback"
			getItemLabel = "ItemLabelCallBack"
 			onChange = "cboChangeHandler" 
		>
		</comboBox>
        </group>
      </tab>
	<?tab idMso="TabHome" visible="true" /?> 
    </tabs>
  </ribbon>
</customUI>

Open in new window


VBA in the 'Ribbon|Control' MOdule:

Sub Onload(ribbon As IRibbonUI)
'Creates a ribbon instance for use in this project
    Set myRibbon = ribbon
    arrCBO2 = Array("Macro1", "Macro3")

End Sub

Sub macro1()
    MsgBox "Hello from sub 'MAcro1'"
End Sub

Sub macro2()
    MsgBox "Hello from sub 'MAcro2'"
End Sub

Sub macro3()
    MsgBox "Hello from sub 'MAcro3'"
End Sub

Open in new window


VBA in 'any' module

Public myRibbon As IRibbonUI
Public arrCBO2() As Variant
Public intCBO1Index As Integer


Sub ItemCountCallback(ByVal control As IRibbonControl, ByRef count)
'Defines the size (row count) of a dynamic combobox display window
  
  Select Case control.ID
    Case "cbo2"
        count = UBound(arrCBO2) + 1
    Case Else
  End Select

End Sub

Sub ItemLabelCallback(ByVal control As IRibbonControl, Index As Integer, ByRef Label)
' Displays the actual text for each row in the dynamic combo
    
    Label = arrCBO2(Index)

End Sub

Sub GetCBOLabel(control As IRibbonControl, Index As Integer, ByRef Label)
    ' Callback get the label for a item
  Select Case control.ID
    Case "cbo2"
        Label = arrCBO2(0)
    Case Else
  End Select
End Sub

Public Sub GetComboText(control As IRibbonControl, ByRef Text)
'Initialise the combo value

    Select Case control.ID
        Case "cbo1"
            Text = "Macro1"
        Case "cbo2"
            Text = arrCBO2(0)
        Case Else
    End Select

End Sub

Sub cboChangeHandler(control As IRibbonControl, Text As String)

    Select Case control.ID
        Case "cbo1"
            Application.Run Text
        Case "cbo2"
            Application.Run Text
        Case Else
    End Select

End Sub

Open in new window


Essentially thgis demonstrates both options ... fixed in the XML and coded within the VBA itself as combos 1 & 2 respectively.

Chris
0
 
DrTribosCommented:
Hi Andy - any further thoughts on the sample file I uploaded in the
sample file I uploaded in the other question I referred you to?
A feature of the code in the sample file is that it allows the number of buttons on the ribbon to be completely dynamic.  It basically defines a set number of tables.  The tables each correspond to a dropdown gallery.  The VBA is dynamic in that it assigns buttons to each gallery depending on the number or rows in the gallery.


Hi Chris,  Appologies, I thought you were suggesting it was possible to add a dropdown menu (as requested in the OP) via the developer tab:
If so then enable the developer ribbon: File | Options | Customise ribbon ... in the right hand window enable the developer tab

I was just curious as I didn't think that was possible... :-)
0
 
AndyAinscowAuthor Commented:
I intend to get onto this next week, sorry for the delays (easter holidays amongst others)
0
 
DrTribosCommented:
Hi Andy - just so you know, my availability will be scarce from the 25th till ~May 10.  

I think that between the sample file and code from Chris you should be sorted,

Cheers,
0
 
AndyAinscowAuthor Commented:
Thanks.  I'm surprised adding a combo is such a difficult task considering that adding buttons / tabs to a ribbon is trivial within winword and that Microsoft use comboboxs on the ribbons as part of the UI.

The utility (at least the version I found and downloaded) doesn't allow a combo to be added via the UI - the XML code needs to be added directly.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now