Adding icons to ribbon

[tiredofit]

I am trying to modify the Ribbon by adding my own custom functionality.

I have accomplished what I want using the CustomUI tool but if I want my buttons to be disabled initially, then enabled at runtime, I feel I must write the XML in VBA.

This is the code:

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

Sub ClearCustRibbon()

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

and it was taken from this website:

https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba

I can disable my buttons simply by adding:

enabled='false'

What the website doesn't show is how to add icons.

I added this line:

image='createreport'

but where do I add the physical icon?

Is it possible and if so, how?

Thanks

 

[jkpieterse]

If this is a ribbon update for one file, you should be putting ribbonX code into the file itself. For this you need the Office ribbonX editor which is a free download. Then use the getEnabled callback and an associated subroutine in your file to handle the enabled state of the button(s). See: Excel: Building an Excel add-in for some pointers.

 

[tiredofit]

If this is a ribbon update for one file, you should be putting ribbonX code into the file itself. For this you need the Office ribbonX editor which is a free download. Then use the getEnabled callback and an associated subroutine in your file to handle the enabled state of the button(s). See: Excel: Building an Excel add-in for some pointers.

Thanks, I'll look into it.

If I understand you correctly, it's not advisable to write XML in the VB Editor, as in the website I showed?

 

[jkpieterse]

You are right. The method you mentioned above overwrites all ribbon customizations of your user. The one I refer to modifies the ribbon just for the workbook; so only when this workbook is opened, will the ribbon changes be displayed. ANy user-specific customizations are left untouched.

 

[tiredofit]

You are right. The method you mentioned above overwrites all ribbon customizations of your user. The one I refer to modifies the ribbon just for the workbook; so only when this workbook is opened, will the ribbon changes be displayed. ANy user-specific customizations are left untouched.

Thanks for clarifying.