Solved

Bind Custom Ribbon to Workbook

Posted on 2016-08-03
11
77 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 14

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 14

Expert Comment

by:DrTribos
ID: 41741513
I don't understand.  Local macros?
0
 
LVL 14

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 14

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

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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

17 Experts available now in Live!

Get 1:1 Help Now