Permanent exit button on Excel ribbon

Question

I have a excel workbook that contains a couple hundred tabs that are all very hidden unless needed. I have it set up so that the user can select a data source from a main sheet and it transfers them to the specific sheet they need. Then as they finish their task they return to the main sheet and all is hidden again. What I need is a way to attach a command button to the ribbon so that it is always there and hides everything but the main sheet.

I have worked with put a button on each sheet and even tried a floating form that stays in the corner. Although these are answers I would prefer to just attach a button to the ribbon and forget about it. But how do you dynamically create a button in the ribbon that will transfer with the workbook so that every user on any computer will have the button available?

You can't create ribbon controls in VBA, but if you use the `CommandBars` API via `Application.CommandBars` it'll create your button under an "add-ins" ribbon tab. Create the button in the workbook's `Workbook_Open` event handler and it will show up when that workbook is opened. A good idea is to also remove the button when the workbook is closed. — Mathieu Guindon, Dec 18 '15 at 15:26
I explored this also, the issue here is that I can't find anything about dynamically creating a add-in that can run a VBA code. Add-ins are prepackage plug & plays that have to be installed or activated. — Hareborn, Dec 18 '15 at 15:41
See http://gregmaxey.mvps.org/word_tip_pages/customize_ribbon_main.html for how to create a ribbon customization which is attached to your workbook — Tim Williams, Dec 18 '15 at 22:39

score 0 · Accepted Answer · answered Nov 18 '16 at 18:13

I found a work around by creating a custom tab and buttons for my workbook. It creates the tab as the workbooks opens and removes it when you close the book.

Private Sub Workbook_Activate()

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='Pats Tools' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup1' label='Month' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Months1' label='Previous' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AccessTableEvents'      onAction='MonthPrevious.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Months2' label='Current' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AccessListEvents'      onAction='MonthCurrent.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Months3' label='Next' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AccessTableEvents'      onAction='MonthNext.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Months4' label='All' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AccessTableEvents'      onAction='AllMonths.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup2' label='Properties' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Properties1' label='All' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='AllProperties.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Properties2' label='Name1' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='Name1.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Properties3' label='Name2' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='Name2.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Properties4' label='Name3' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='Name3.ClearSheet'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Properties5' label='Name4' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='Name4.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='Properties6' label='Name5' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='Name5.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup4' label='Edit Task' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='ActionButton1' label='Type' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='AddTypemod.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='ActionButton2' label='Section' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='AddSectionMod.calling'/>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='ActionButton3' label='Empty' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='BlogHomePage'      onAction='CedarCreek.calling'/>" & 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

a note: you can line break in VBA with an underscore instead of "& vbNewLine" at the end of the line and start the next line with "&" instead of "ribbonXML = ribbonXML + ". It's less noisy to look at, but there is a total line limitation. Here is an example discussion: http://stackoverflow.com/questions/19422064/break-a-long-sql-vba-statement-into-multiple-lines — n8., Nov 18 '16 at 18:23

Permanent exit button on Excel ribbon

1 Answers1