?
Solved

Bind Custom Ribbon to Workbook

Posted on 2016-08-03
11
Medium Priority
?
1,422 Views
Last Modified: 2016-08-08
I have created a custom ribbon in Excel 2010.  It has all of the standard stuff plus one additional tab.  This tab has calls to macros specific to a particular workbook, and it makes no sense to have it up for any other reason.  I would like this tab to come up when anyone opens this workbook (or any copy) and have the tab disappear if the workbook is not up.

Is there a way to accomplish this?  Are there commands for testing if a particular ribbon tab exists, and if not, importing that tab when the workbook starts up?

All of this functionality used to be buttons, but they were taking up too much real estate on my worksheet.  I was hoping to make things simpler by replacing the buttons with ribbon commands, but it seems like the ribbon is associated with a user rather than a workbook.

Any help on finding VBA commands that test for ribbon tabs, import them if they don't exist, and delete them when the program closes would be much appreciated.
0
Comment
Question by:koughdur
  • 5
  • 4
  • 2
11 Comments
 
LVL 15

Expert Comment

by:DrTribos
ID: 41741462
Search for: Greg Maxey - Custom Ribbon.  

I suspect that you will need to put the ribbon into an XL addin and use ribbon invalidate to destroy and rebuild the ribbon based on application events - sheet / window / workbook _Change
0
 

Author Comment

by:koughdur
ID: 41741498
I found an even bigger problem:  I want to assign local macros to the Ribbon commands.  However, it appears that it is only possible to assign macros from a specific workbook.  So everyone who wants to run my workbook has to have access to the workbook where the macros are located as well.

I guess it's back to the ugly gray buttons.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41741513
I don't understand.  Local macros?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:DrTribos
ID: 41741522
0
 

Author Comment

by:koughdur
ID: 41741582
DrTribos,

Thanks, but I'm not trying to share macros.  This is a highly organized workbook with special sheets that do special things, e.g. collect data from a database, plot data, manage scripting, etc.  So I'm not trying to deploy general purpose macros via add-ins, etc.

This should be a Visual Studio or web-based app but our customer base won't touch anything that doesn't start and end with Excel.  They like to mess with the data however much you try to give them exactly what they want in exactly the form they asked for.

I was hoping that the Ribbon was tied to the Workbook.  However, it seems that Ribbons are tied to the user and appear whenever they bring up any Excel workbook.  So...

(Sung to the tune of 'Tiny Bubbles')

Ugly buttons
On my sheet
Make it look chintzy
Make it look cheap.

Ugly buttons
Make it gray all over
With a stylin' that reminds me
Of the ancient start of time.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41741606
If you use the CustomUI Editor the ribbon will be tied to the workbook.  See: http://gregmaxey.mvps.org/word_tip_pages/customize_ribbon_main.html

Also, Andy Pope made a pretty cool tool... here: http://www.andypope.info/vba/ribboneditor.htm or http://www.andypope.info/vba/ribboneditor_2010.htm
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 41742195
koughdur,

You can customize the ribbon to call macros in a specific workbook.   The ribbon is controlled by XML code that you can add to your workbook to create new "Tabs", "Groups" and "Buttons".  Then, using VBA, you can link macros to those specific buttons.  The attached workbook, has a new Tab called "Idarac Functions".  Within that tab is a Group called "Special Functions".   Within that Group are three buttons that are unique to this workbook.  Each button runs a macro that exists in the workbook.  

For your project, you could build a master workbook that has the special tab and button(s) for your macros that call generic macro names.   This workbook could be copied over and over again for each project.  Then, within each of your projects the macros can be different depending on the client's needs.

To add the XML to a workbook requires an XML Editor.   The one I have found that works best for me is the "Custom UI Editor For Microsoft Office".  You can download it from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx 

The syntax is case sensitive and you must follow the Office Custom UI Schema.   The example below should be enough to accomplish your task but there are books available that go into great detail on the capabilities of customizing the Office Ribbon.   The one I have used is "RibbonX – Customizing the Office 2007 Ribbon" by Robert Martin, Ken Puls and Teresa Hennig.

The XML code to create the new Tab on the ribbon is:
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon> 
		<tabs> 
			<tab id="IDARAC" label="Idarac Functions"> 				
				<group id="spFunctions" label="Special Functions"> 

					<button id="XXXX" 
					label="XXXX Tool"
                                        size = "large" 
					onAction="IDARAC_Handler" 
					screentip="Run the XXXX Tool"
					supertip="This button will execute the Special Functions XXXX Tool"
					imageMso="CalendarToolSelectDate" /> 

					<button id="YYYY" 
					label="YYYY Tool"
                                        size = "large" 
					onAction="IDARAC_Handler" 
					screentip="Run the YYYY Tool"
					supertip="This button will execute the Special Functions YYYY Tool"
					imageMso="Pushpin" /> 

					<button id="ZZZZ" 
					label="ZZZZ Tool"
                                        size = "large" 
					onAction="IDARAC_Handler" 
					screentip="Run the ZZZZ Tool"
					supertip="This button will execute the Special Functions ZZZZ Tool"
					imageMso="CheckWorkflow" /> 

				</group>  
			</tab> 
		</tabs> 
	</ribbon> 
</customUI>

Open in new window

The VBA code associated with the new ribbon buttons is:
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : IDARAC_Handler
' Author    : Jerry Paladino on EE
' Question  : Q_28460080
' Date      : 06/21/2014
' Purpose   : Main Routine RibbonX Handler to call specific routines
'---------------------------------------------------------------------------------------
'
Sub IDARAC_Handler(control As IRibbonControl)
       
    Select Case UCase(control.ID)       ' which button in the ribbon the user clicked
        Case "XXXX"                     ' user clicked the XXXX Button
            Call myMacro_XXXX
        Case "YYYY"                     ' user clicked the YYYY Button
            Call myMacro_YYYY
        Case "ZZZZ"                     ' user clicked the ZZZZ Button
            Call myMacro_ZZZZ
        Case Else
            MsgBox "Unknown or Under Construction"
    End Select
    
End Sub


'---------------------------------------------------------------------------------------
' Procedure : myMacro_XXXX
' Author    : Jerry Paladino on EE
' Question  : Q_28460080
' Date      : 06/21/2014
' Purpose   : Stub for future macro
'---------------------------------------------------------------------------------------
'
Sub myMacro_XXXX()

    'Enter future macro code here
    MsgBox "Hello World - My name is myMacro_XXXX"

End Sub

'---------------------------------------------------------------------------------------
' Procedure : myMacro_YYYY
' Author    : Jerry Paladino on EE
' Question  : Q_28460080
' Date      : 06/21/2014
' Purpose   : Stub for future macro
'---------------------------------------------------------------------------------------
'
Sub myMacro_YYYY()

    'Enter future macro code here
    MsgBox "Hello World - My name is myMacro_YYYY"

End Sub

'---------------------------------------------------------------------------------------
' Procedure : myMacro_ZZZZ
' Author    : ProdOps on EE
' Question  : Q_28460080
' Date      : 06/21/2014
' Purpose   : Stub for future macro
'---------------------------------------------------------------------------------------
'
Sub myMacro_ZZZZ()

    'Enter future macro code here
    MsgBox "Hello World - My name is myMacro_ZZZZ"

End Sub

Open in new window

I have provided this solution in another EE question located at:EE-28460080
EE-Q-28460080-RibbonX.xlsm
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 41742203
Also, just a side note on the comments about grey buttons...  From the Insert Ribbon in the Illustrations section you can select any of the Shapes and assign a macro to them.  You can add color, 3-D effects, shadows, etc... so your sheets are cluttered with drab grey buttons.
0
 

Author Comment

by:koughdur
ID: 41742970
Thanks.  This looks very doable.  Through the interface I can associate macros with ribbon commands, but the full path is hardwired in.  By going into the XML I will be able to reference local macros so the ribbon will be tied to the workbook.
0
 

Author Comment

by:koughdur
ID: 41742975
Regarding pix vs. buttons:  I've thought about that, but the nice thing about buttons is that you can see when you click them.  I've seen code for swapping between two images (depressed and undepressed) so that users can see when they click a pic.  I might have gone there if I had to stick with the buttons.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41743483
Instead of the ms custom ui editor, try the tool from Andy Pope. It rocks. Look up for link.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

840 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