Add a tab to the Excel ribbon

user_19906497 0
Viewed 54

I am using the code as below which i have adapted from the Net and it runs but does not add the button to the ribbon. I am using Excel 2016 Professional Plus and i wondered if this is only possible using 365 and not 2016 or my code is wrong. Any advice will be gratefully received.

Many thanks

regards

Spike

Sub CustomRibbon()
Dim hFile As Long
Dim path As String
Dim fileName As String
Dim ribbonXml As String
Dim user As String 

    hFile = FreeFile 
    user = Environ("Username")
    path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\" 
    fileName = "Excel.officeUI"  
    
    ribbonXml = "<mso:customUI onLoad=""RibbonOnLoad"" xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & vbNewLine 
    
    ribbonXml = ribbonXml + "  <mso:ribbon>" & vbNewLine
    ribbonXml = ribbonXml + "    <mso:qat/>" & vbNewLine
    ribbonXml = ribbonXml + "    <mso:tabs>" & vbNewLine
    
    ribbonXml = ribbonXml + "      <mso:tab id=""CustomTab"" label=""SP"" insertBeforeQ=""mso:TabInsert"">" & vbNewLine
    ribbonXml = ribbonXml + "        <mso:group id=""GroupSP"" label=""SP"" autoScale=""true"">" & vbNewLine
    ribbonXml = ribbonXml + "          <mso:button id=""Report"" label=""Testing"" & vbNewLine"
    ribbonXml = ribbonXml + "imageMso=""ShapeDownArrow""      onAction=""Mac_1_run""/>" & vbNewLine
    
    ribbonXml = ribbonXml + "        </mso:group>" & vbNewLine
    ribbonXml = ribbonXml + "      </mso:tab>" & vbNewLine
    ribbonXml = ribbonXml + "    </mso:tabs>" & vbNewLine
    ribbonXml = ribbonXml + "  </mso:ribbon>" & vbNewLine
    ribbonXml = ribbonXml + "</mso:customUI>"
    
    Open path & fileName For Output Access Write As hFile
    Print #hFile, ribbonXml
    Close hFile

End sub
vba excel
1 Answers

Add a Tab to the Ribbon

  • Note that this will mess up whatever you have previously manually customized including the Quick Access Toolbar. Make a backup of your Excel.officeUI file before using this code.

A Quick Fix

Sub CustomRibbon()

    Const OfficePath As String = "\Microsoft\Office\"
    Const FileName As String = "Excel.officeUI"
    
    Dim LocalPath As String: LocalPath = Environ("LOCALAPPDATA")
    Dim FilePath As String: FilePath = LocalPath & OfficePath & FileName
    
    Dim ribbonXml As String
    
    ribbonXml = "<mso:customUI onLoad=""RibbonOnLoad"" xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & vbLf
    ribbonXml = ribbonXml & "  <mso:ribbon>" & vbLf

' These are irrelevant at this moment.
' 'qat' is short for Quick Access Toolbar
'    ribbonXml = ribbonXml & "    <mso:qat>" & vbLf
'    ribbonXml = ribbonXml & "      <mso:sharedControls>" & vbLf
'    ribbonXml = ribbonXml & "      </mso:sharedControls> & vbLf
'    ribbonXml = ribbonXml & "    </mso:qat>" & vbLf
    
    ribbonXml = ribbonXml & "    <mso:tabs>" & vbLf
    ribbonXml = ribbonXml & "      <mso:tab id=""CustomTab"" label=""SP"" insertBeforeQ=""mso:TabInsert"">" & vbLf
    ribbonXml = ribbonXml & "        <mso:group id=""GroupSP"" label=""SP"" autoScale=""true"">" & vbLf
    ribbonXml = ribbonXml & "          <mso:button id=""Report"" label=""Testing"" imageMso=""ShapeDownArrow"" onAction=""Mac_1_run""/>" & vbLf
    ribbonXml = ribbonXml & "        </mso:group>" & vbLf
    ribbonXml = ribbonXml & "      </mso:tab>" & vbLf
    ribbonXml = ribbonXml & "    </mso:tabs>" & vbLf
    ribbonXml = ribbonXml & "  </mso:ribbon>" & vbLf
    ribbonXml = ribbonXml & "</mso:customUI>"
    
    Dim hFile As Long: hFile = FreeFile
    
    Open FilePath For Output Access Write As hFile
        Print #hFile, ribbonXml
    Close hFile

End Sub
user_98140690
Related