0

I'm trying to make a custom tab for others with some macro's.

I found this article: How to add a custom Ribbon tab using VBA?

In this article, the reply of Roi-Kyi Bryant is very helpful and works great for me. But I have one question about it. Can I reorder the tabs with this VBA code? I know its possible to move it by myself when I click on the ribbon with the right button on the mouse and choose edit ribbon.

any ideas?

The code I use from Roi-Kyi Bryant is as following:

Sub LoadCustRibbon()

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='Reports' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Reports' autoScale='true'>" &             vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='runReport' label='PTO' "   & vbNewLine
ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='GenReport'/>" & 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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jelle van der Heijden
  • 47
  • 5
  • 1
    Usually `insertBeforeQ` (qualified identifier of control to insert before) or `insertBeforeMso` (identifier of built-in control to insert before) specifies the tab where to insert your new tab. Also have a look [here](https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/141f881c-a5a4-473f-9449-55d3d36579ed) and the [MS-CUSTOMUI: Custom UI XML Markup Specification](https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/31f152d6-2a5d-4b50-a867-9dbc6d01aa43) – Pᴇʜ Apr 06 '20 at 12:27
  • @Pᴇʜ after doing some more research i got it working. Thanks. Now when I run the code it will completely reset my ribbon, could I made it so it only adds it and not completely resets my ribbon? – Jelle van der Heijden Apr 10 '20 at 08:48
  • Also if I make any changes (the normal way) after installing this new ribbon. The buttons I added via VBA are gone – Jelle van der Heijden Apr 10 '20 at 08:57

0 Answers0