Solved

Macro links on ribbon

Posted on 2014-04-08
17
292 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
  • 7
  • 6
  • 4
17 Comments
 
LVL 14

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 14

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
 
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 14

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 44

Author Comment

by:AndyAinscow
ID: 39993581
Neat.
0
 
LVL 14

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 14

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 14

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now