Bind Custom Ribbon to Workbook

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.
koughdurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrTribosCommented:
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
koughdurAuthor Commented:
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
DrTribosCommented:
I don't understand.  Local macros?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DrTribosCommented:
0
koughdurAuthor Commented:
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
DrTribosCommented:
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
Jerry PaladinoCommented:
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
Jerry PaladinoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
koughdurAuthor Commented:
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
koughdurAuthor Commented:
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
DrTribosCommented:
Instead of the ms custom ui editor, try the tool from Andy Pope. It rocks. Look up for link.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.