Adding commandbar controls to the Ribbon QAT via VBA ?

[Jaafar Tribak]

I am experimenting with programming the ribbon and I want to add at runtime two new commandbar controls to the QAT namely the FileNewDefault and the Spelling controls.

The code below generates the xml string and writes the xml string to the Excel.OfficeUI file.

However the controls do not load and the QAT remains unaffected even after closing and reopening excel.

When I open the OfficeUI file, it contains the new xml but has no effect.

I am sure I am missing something here as this is new territory to me .. Does anyone knows what is going on ?

Regards.

Sub AddControlToQAT()

    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=" & Chr(34) & "http://schemas.microsoft.com/office/2009/07/customui" & Chr(34) & ">" & vbNewLine
    ribbonXML = ribbonXML + "  <mso:ribbon>" & vbNewLine ''
    ribbonXML = ribbonXML + "    <mso:qat>" & vbNewLine ''
    ribbonXML = ribbonXML + "      <mso:sharedControls>" & vbNewLine ''
    ribbonXML = ribbonXML + "           <mso:control idQ=" & Chr(34) & "mso:FileNewDefault" & Chr(34) & " Visible=" & Chr(34) & "true" & Chr(34) & "/>" & vbNewLine
    ribbonXML = ribbonXML + "           <mso:control idQ=" & Chr(34) & "mso:Spelling" & Chr(34) & " Visible=" & Chr(34) & "true" & Chr(34) & "/>" & vbNewLine
    ribbonXML = ribbonXML + "      </mso:sharedControls>" & vbNewLine ''
    ribbonXML = ribbonXML + "    </mso:qat>" & vbNewLine ''
    ribbonXML = ribbonXML + "   /mso:ribbon>" & vbNewLine ''
    ribbonXML = ribbonXML + "</mso:customUI>" & vbNewLine ''
    
    Debug.Print ribbonXML
    
    Open path & fileName For Output Access Write As hFile
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hFile]#hFile[/URL] , ribbonXML
    Close hFile


End Sub

 

[Jaafar Tribak]

The forum doesnt show the xml code .. here is a snapshot of the code :

 

[Jaafar Tribak]

testing

 

[Jaafar Tribak]

Sub LoadCustRibbon1()
Dim hFile As LongDim path As String, fileName As String, ribbonXML As String, user As String
hFile = FreeFileuser = Environ("Username")path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"fileName = "Excel.officeUI"
ribbonXML = "" & vbNewLine
ribbonXML = ribbonXML + "  " & vbNewLineribbonXML = ribbonXML + "    " & vbNewLineribbonXML = ribbonXML + "    " & vbNewLineribbonXML = ribbonXML + "      " & vbNewLineribbonXML = ribbonXML + "        " & vbNewLineribbonXML = ribbonXML + "          " & vbNewLineribbonXML = ribbonXML + "        " & vbNewLineribbonXML = ribbonXML + "      " & vbNewLineribbonXML = ribbonXML + "    " & vbNewLineribbonXML = ribbonXML + "  " & vbNewLineribbonXML = ribbonXML + ""
ribbonXML = Replace(ribbonXML, "'", Chr(34))
Debug.Print ribbonXMLOpen path & fileName For Output Access Write As hFilePrint [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hFile]#hFile[/URL] , ribbonXMLClose hFile
End Sub

 

[Genki]

Hi Jaafar,

I like your approach. Did you get a solution for this?