Ribbon Macro works in 2016 but not 2013 excel

[spoony40]

I have the below code that somehow only works in excel 2016 but it should work in 2013 as well. Can someone use it on excel 2013 and see whether its a security issue or some other problem:

Just past it in ThisWorkbook in VBA

Private Sub Workbook_Open()

Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String, user As String

hFile = FreeFile
user = Environ("Username")
path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
fileName = "Excel.officeUI"

ribbonXML = "<mso:customUI      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='reportTab' label='Macros' insertBeforeQ='mso:TabFormat'>" & vbNewLine

ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Reports' >" & vbNewLine 'autoScale='true'

ribbonXML = ribbonXML + "          <mso:button id='OilGas' label='OilGas Matrix' " & vbNewLine     'New button
ribbonXML = ribbonXML + "imageMso='CreateTable'  size='large'    onAction='OilGasMatrixSummary'/>" & 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>"

ribbonXML = Replace(ribbonXML, """", "")

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String, user As String

hFile = FreeFile
user = Environ("Username")
path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
fileName = "Excel.officeUI"

ribbonXML = "<mso:customUI           xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
"<mso:ribbon></mso:ribbon></mso:customUI>"

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

End Sub

 

[RoryA]

Can I ask why you're doing that rather than just adding the XML to the CustomUI part of that workbook?

 

[spoony40]

I need to send it as an addin to many people and they dont have customUI installed. Most people are not even allowed to turn macro on T_T

I can't get customUI installed either...

 

[RoryA]

The users don't need anything installed, other than Excel. The CustomUI part is part of the workbook.

If they can't turn on macros how would your code ever run?

 

[spoony40]

But i need the macros on the ribbon to run on many workbooks. I dono how else to do it. The code only works on my version of excel and doesn't work on other computers which i dont know why, whether its security or the code is the problem.

 

[RoryA]

The point is that you add that XML to the workbook once. It is then saved with it and whenever the add-in is loaded, the menu items will appear on the Ribbon automatically.