Bind Custom Ribbon to Workbook

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

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.

I don't understand.  Local macros?

Search for: how to deploy excel   https://www.google.com.au/search?safe=off&espv=2&q=how+to+deploy+excel+macro

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.

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

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>

Select allOpen 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

Select allOpen in new window

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

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.

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.

Instead of the ms custom ui editor, try the tool from Andy Pope. It rocks. Look up for link.