The problem

Bill has created several simple ribbon customizations in Excel with buttons that hyperlink to webpages, mailto:, Word documents or worksheets. The workbooks are saved in a macro-free format and shared across the enterprise. Obviously without VBA, the ribbon callbacks won't fire.

Bill is wondering, if somehow the hyperlinks could work with XML code alone (!)

The Workaround

Unfortunately, only custom backstage tabs (first introduced with Office 2010) offer working text and image hyperlinks without VBA callbacks. Read about backstage controls here.

Ribbon VBA callbacks can be fired from macro-free workbooks, if stored in your Personal.xlsb workbook. Here is how:

Let's start by creating an XML ribbon customization code using Ribbon Commander VBA. 

---

Option Explicit

Public Sub CreateUI()

    Const lButtons As Long = 4
    Dim I As Long

    With rxCustomUI.defaultInstance
        .Clear
        .loadImage = .make_delegate_sv("LoadImageFromThisWorkbook")
       
        With .ribbon.tabs.Add(New rxTab)
            .Label = "S1 Demo"
            
            With .groups.Add(New rxGroup)
                .Label = "Hyperlinks"
                
                For I = 0 To lButtons
                    With .Buttons.Add(New rxButton)
                        .Size = rxsLarge
                        .image = Array("rc", "linkedin", "s1", "email", "tab-remove")(I)
                        .Label = Array("Visit RC Website", "RC Linkedin Group", "Visit Spreadsheet1", "Email Petros", "Close Workbook")(I)
                        .OnAction = rxCustomUI.defaultInstance.make_delegate_sv("Navigate_OnAction")
                        .Tag = Array("http://www.ribboncommander.com", _
                                     "https://www.linkedin.com/grp/home?gid=5146055", _
                                     "http://www.spreadsheet1.com", _
                                     "mailto:info@spreadsheet1.com", _
                                     "<CLOSE>")(I)
                    End With
                    If I < lButtons Then .separators.Add New rxSeparator
                Next I
                
            End With 'Group
        End With 'Ribbon
        
        .Refresh
        Debug.Print .serializeToXml(SchemaXml_Type_2007)
    End With
    
End Sub

<!--XML code created by the Ribbon Commander framework v1.1-->
<customUI loadImage="LoadImageFromThisWorkbook" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="RX4BC31C-D2DD-4DBC-8AD3-26D4CB9D1839" label="S1 Demo">
        <group id="RXCF60DC-54B6-4DB5-80ED-C5D51149B09A" label="Hyperlinks">
          <button image="rc" label="Visit RC Website" id="RX24BBB3-BC1E-40A8-819E-5B6CDF161E4C" tag="http://www.ribboncommander.com" size="large" onAction="Navigate_OnAction"></button>
          <separator id="RXA532D3-39A2-4FEF-92E5-0ADC4D25E62B"></separator>
          <button image="linkedin" label="RC Linkedin Group" id="RX427FD0-E5DC-4D7A-AD11-EC00AA721ACF" tag="https://www.linkedin.com/grp/home?gid=5146055" size="large" onAction="Navigate_OnAction"></button>
          <separator id="RXF012CB-D3DC-46CD-B58D-BA553FBA8F0F"></separator>
          <button image="s1" label="Visit Spreadsheet1" id="RX24FCA2-3658-4439-A894-BBCEECDAB173" tag="http://www.spreadsheet1.com" size="large" onAction="Navigate_OnAction"></button>
          <separator id="RXD0804D-4A87-4FC7-86BF-F7F6524A4B50"></separator>
          <button image="email" label="Email Petros" id="RX859DCC-36E8-41CD-B28C-733B796F6722" tag="mailto:info@spreadsheet1.com" size="large" onAction="Navigate_OnAction"></button>
          <separator id="RXBDEE55-2F60-4072-938F-E4CD7BED46B6"></separator>
          <button image="tab-remove" label="Close Workbook" id="RX2F0CD8-C59E-45BB-A041-ECDB299C131A" tag="&lt;CLOSE>" size="large" onAction="Navigate_OnAction"></button>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

XML code and icons imported into customUI area of workbook

---

Custom Ribbon UI in a macro-free XLSX workbook

---

Copy Ribbon Callbacks to your Personal Macro Workbook

The personal workbook (Personal.xlsb) is opened as a hidden workbook every time you start Excel. Excel saves the personal workbook as Personal.xlsb, in the following folder on Windows 7 and Windows Vista computers:

C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART

The easiest way to create Personal.xlsb is to record a macro and save it in the Personal Macro Workbook. Macros stored in a Personal workbook are not specific to one project, but available to all open workbooks. Read more here

Ribbon callbacks stored in the macro-free workbook can be run from Personal.xlsb, if there is a reference to your hidden Personal workbook. Unfortunately, references can only be stored inside VBA projects and XLSX workbooks don't have one. So, the reference has to be set when the macro-free workbook is opened. Here is the code needed:

Option Explicit

'ThisWorkbook module

Private XLApp As CExcelEvents

Private Sub Workbook_Open()
    Set XLApp = New CExcelEvents
End Sub

Option Explicit

' mCallbacks standard module

Public Sub AddReferenceToActiveWorkbook()

    On Error Resume Next

    ActiveWorkbook.VBProject.References.AddFromFile "C:\Users\" & Application.UserName & "\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB"

End Sub

Public Sub Navigate_OnAction(control As IRibbonControl)

    Dim sTag As String
    
    On Error Resume Next

    sTag = control.Tag
    
    Select Case sTag
        Case "<CLOSE>"
            ActiveWorkbook.Close 'SaveChanges:=True
        Case Else
            ActiveWorkbook.FollowHyperlink sTag
    End Select
    
End Sub

Public Sub NavigateGallery_OnAction(control As IRibbonControl, id As String, Index As Integer)
    ' if a Gallery is used
End Sub

Option Explicit

' CExcelEvents class module

Private WithEvents App As Application

Private Sub Class_Initialize()

    Set App = Application
    
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    
    Application.OnTime (Now + TimeValue("00:00:02")), "AddReferenceToActiveWorkbook"
    
End Sub

In summary, the application event in Personal.xlsb sets a temporary reference to the personal workbook in every macro-free workbook opened. Since a VBA project cannot be saved in an XLSX file, the reference cannot persist. It has to be added every time a macro-free workbook is opened. 

With the reference properly set, the Navigate_OnAction callback declared in XML ribbon customization will be executed from the Personal.xlsb. Without a reference to valid VBA code, a runtime error will occur when a ribbon button is clicked in an XLSX workbook.

Please note that custom tabs ribbon will only persist in the ribbon whilst the workbook is active. You should see your custom tab reappear when the workbook is activated. That's the default desired behaviour for a workbook ribbon. To have a ribbon available constantly you either need to save the workbook as an addin or use a shared namespace.

---

Embed VBA code in a macro-free workbook!

There is a major drawback to the trick described: distribution! What if you want to distribute a macro-free workbook with working ribbon hyperlinks to several users?

Use an add-in, instead of a Personal workbook, and distribute both files, or embed the Personal.xlsb workbook into a macro-free XLSX workbook. Here are the steps required:

• Go to INSERT ribbon tab, Text group
• Click OBJECT
• Click 'Create from File' tab
• Select the macro-enabled file you want to embed
• Mark the 'Link to file' checkbox
• Click OK
  
  

Excel will detect that the macro-free XLSX workbook contains a link to an external source (OLE object with VBA code) and will display a warning each time the workbook is opened:

"This workbook contains links to one or more external sources that could be unsafe" 

Click UPDATE to allow the VBA to create the reference to the ribbon callbacks. The workbook with VBA must be accessible from the XLSX workbook e.g. in a shared folder, if the XLSX file is used by several users.

---

Extend VBA using Ribbon Commander

The Ribbon Commander framework rxFramework.FollowHyperlink method is similar to the Workbook.FollowHyperlink method in VBA, but it executes much faster in a separate thread without using any cached documents. Read more here

Powerpoint doesn't offer a ThisPresentation object. An active presentation should be open to use VBA's ActivePresentation.FollowHyperlink method. rxFramework.FollowHyperlink can be used in PPT add-ins without any presentation being open. 

Macro Mover Add-in powered by RC

Personal workbook must be shared, so it can be used with multiple Excel instances

---

A personal workbook must be shared, so it can be used with multiple Excel instances without read-only warnings. However, macros cannot be updated in shared workbooks. The Macro Mover add-in powered by Ribbon Commander can unlock VBA in shared workbooks. Read how here.

Download

Download samples and Personal.xlsb

---

comments powered by Disqus