Solved

Macro links on ribbon

Posted on 2014-04-08
17
301 Views
Last Modified: 2014-04-22
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
Comment
Question by:AndyAinscow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 15

Accepted Solution

by:
DrTribos earned 125 total points
ID: 39985629
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
 
LVL 44

Author Comment

by:AndyAinscow
ID: 39990912
Thanks, I've read the comments in the link.  I'll try to look at that UI utility soon.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39990940
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 44

Author Comment

by:AndyAinscow
ID: 39990948
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
 
LVL 15

Expert Comment

by:DrTribos
ID: 39991028
Same applies! You've been warned ;-)
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39993448
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
 
LVL 44

Author Comment

by:AndyAinscow
ID: 39993540
>>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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39993544
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
 
LVL 44

Author Comment

by:AndyAinscow
ID: 39993581
Neat.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39993657
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
 
LVL 44

Author Comment

by:AndyAinscow
ID: 39993664
>>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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39994092
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
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 125 total points
ID: 39994585
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
 
LVL 15

Expert Comment

by:DrTribos
ID: 40005580
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
 
LVL 44

Author Comment

by:AndyAinscow
ID: 40005860
I intend to get onto this next week, sorry for the delays (easter holidays amongst others)
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 40005876
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
 
LVL 44

Author Closing Comment

by:AndyAinscow
ID: 40016759
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question