Как добавить пользовательскую вкладку Ribbon с помощью VBA?

Я ищу способ добавить пользовательскую вкладку в ленте Excel, которая будет содержать несколько кнопок. Я оценил некоторые ресурсы, обращаясь к ним через Google, но все выглядят изворотливыми и возмутительно сложными.

Что такое быстрый и простой способ сделать это? Я хочу, чтобы новая вкладка загружалась, когда мой VBA загружается в Excel..

ОБНОВЛЕНИЕ: Я попробовал этот пример из здесь, но получить последнюю требуемую ошибку:

Public Sub AddHighlightRibbon()
Dim ribbonXml As String

ribbonXml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
ribbonXml = ribbonXml + "  <mso:ribbon>"
ribbonXml = ribbonXml + "    <mso:qat/>"
ribbonXml = ribbonXml + "    <mso:tabs>"
ribbonXml = ribbonXml + "      <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"
ribbonXml = ribbonXml + "        <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"
ribbonXml = ribbonXml + "          <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "
ribbonXml = ribbonXml + "imageMso=""DiagramTargetInsertClassic"" onAction=""ToggleManualTasksColor""/>"
ribbonXml = ribbonXml + "        </mso:group>"
ribbonXml = ribbonXml + "      </mso:tab>"
ribbonXml = ribbonXml + "    </mso:tabs>"
ribbonXml = ribbonXml + "  </mso:ribbon>"
ribbonXml = ribbonXml + "</mso:customUI>"

ActiveProject.SetCustomUI (ribbonXml)
End Sub

Ответ 1

AFAIK вы не можете использовать VBA Excel для создания пользовательской вкладки в ленте Excel. Однако вы можете скрыть/сделать видимым ленточный компонент с помощью VBA. Кроме того, ссылка, о которой вы говорили выше, предназначена для MS Project, а не для MS Excel.

Я создаю вкладки для своих приложений/надстроек Excel, используя эту бесплатную утилиту под названием Custom UI Editor.

Изменить: для размещения нового запроса OP

Учебник

Вот короткий учебник, как и обещал:

После того, как вы установили пользовательский редактор пользовательского интерфейса (CUIE), откройте его и нажмите "Файл" | Откройте и выберите соответствующий файл Excel. Убедитесь, что файл Excel закрыт, прежде чем открывать его через CUIE. В качестве примера я использую новый рабочий лист.
Щелкните правой кнопкой мыши, как показано на рисунке ниже, и нажмите "Отдельный пользовательский интерфейс Office 2007". Он будет вставлять "customUI.xml"
Далее Нажмите на меню Вставить | Пример XML | Пользовательская вкладка. Вы заметите, что базовый код будет автоматически сгенерирован. Теперь вы готовы изменить его в соответствии с вашими требованиями.
Осмотрите код

label="Custom Tab": Замените "Пользовательская вкладка" на имя, которое вы хотите предоставить своей вкладке. Пока назовите это "Джером".

В приведенной ниже части добавляется настраиваемая кнопка.
```
<button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
```
imageMso: Это изображение, которое будет отображаться на кнопке. "HappyFace" - это то, что вы сейчас увидите. Здесь можно загрузить больше идентификатора изображения.

onAction="Callback": "Обратный вызов" - это имя процедуры, которая выполняется при нажатии кнопки.

Demo

При этом создайте 2 кнопки и назовите их "JG Button 1" и "JG Button 2". Позвольте сохранить счастливое лицо как образ первого и позволить сохранить "Солнце" для второго. Измененный код теперь выглядит следующим образом:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab id="MyCustomTab" label="Jerome" insertAfterMso="TabView">
<group id="customGroup1" label="First Tab">
<button id="customButton1" label="JG Button 1" imageMso="HappyFace" size="large" onAction="Callback1" />
<button id="customButton2" label="JG Button 2" imageMso="PictureBrightnessGallery" size="large" onAction="Callback2" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

Удалите весь код, который был сгенерирован в CUIE, а затем вставьте вышеуказанный код вместо этого. Сохраните и закройте CUIE. Теперь, когда вы откроете файл Excel, он будет выглядеть следующим образом:

Теперь часть кода. Откройте редактор VBA, вставьте модуль и вставьте этот код:

Public Sub Callback1(control As IRibbonControl)

    MsgBox "You pressed Happy Face"

End Sub

Public Sub Callback2(control As IRibbonControl)

    MsgBox "You pressed the Sun"

End Sub

Сохраните файл Excel в качестве файла с поддержкой макроса. Теперь, когда вы нажимаете "Смайлик" или "Солнце", вы увидите соответствующее окно сообщения:

Надеюсь, это поможет!

Ответ 2

Я смог выполнить это с помощью VBA в Excel 2013. Никаких специальных редакторов не требовалось. Все, что вам нужно, это редактор кода Visual Basic, доступ к которому можно получить на вкладке "Разработчик" . Вкладка "Разработчик" по умолчанию не отображается, поэтому ее необходимо включить в меню "Файл" > "Параметры" > "Настроить ленту". На вкладке "Разработчик" нажмите кнопку "Visual Basic". Запустится редактор кода. Щелкните правой кнопкой мыши в панели Project Explorer слева. Нажмите меню вставки и выберите модуль. Добавьте оба модуля ниже в новый модуль.

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

Выполните вызов LoadCustRibbon sub в открывшемся Wookbook и вызовите элемент ClearCustRibbon в событии Before_Close файла кода ThisWorkbook.

Ответ 3

Я боролся, как сумасшедший, но на самом деле это правильный ответ. Для чего это стоит, я пропустил это:

Как говорят другие, нельзя создать ленту CustomUI с VBA, однако, вам не нужно!
Идея заключается в том, что вы создаете свой ленточный код xml с помощью файла Excel > Параметры > Настроить ленту и затем экспортируйте ленту в файл .customUI(это просто файл txt с xml в нем).
Теперь идет трюк: вы можете включать код .customUI в ваш .xlsm файл, используя инструмент MS, на который они ссылаются здесь, копируя код из. файл customUI
Как только он включен в файл .xlsm, каждый раз, когда вы его открываете, лента, которую вы определили, добавлен к ленте пользователя - но используйте < лента startFromScratch = "false" > или вы потеряете остальную часть ленты. При выходе из рабочей книги лента удаляется.
Здесь просто, создайте ленту, скопируйте xml-код, специфичный для вашей ленты из файла .customUI, и поместите его в оболочку, как показано выше (... <tabs> your xml </tabs...)

Кстати, страница, которая объясняет это на сайте Рона, теперь находится на http://www.rondebruin.nl/win/s2/win002.htm

И вот его пример о том, как вы включаете/выключаете кнопки на ленте http://www.rondebruin.nl/win/s2/win013.htm

Для других примеров XML-лент также см. http://msdn.microsoft.com/en-us/library/office/aa338202%28v=office.12%29.aspx

Ответ 4

Ответы здесь специфичны для использования пользовательского Редактора пользовательского интерфейса. Я потратил некоторое время на создание интерфейса без этой замечательной программы, поэтому я документирую решение здесь, чтобы помочь кому-то еще решить, нужен ли им этот пользовательский редактор UI или нет.

Я наткнулся на следующую веб-страницу справки Microsoft - https://msdn.microsoft.com/en-us/library/office/ff861787.aspx. Это показывает, как настроить интерфейс вручную, но у меня были некоторые проблемы при указании на мой пользовательский код надстройки.

Чтобы заставить кнопки работать с вашими настраиваемыми макросами, настройте макрос в своих.xlam subs, которые вызывают, как описано в этом ответе SO. Вызов макроса excel из ленты. В принципе, вам нужно добавить параметр "control as IRibbonControl" в любой модуль, указанный на вашем ленточном xml. Кроме того, ваш XML файл ленты должен иметь синтаксис onAction = "myaddin! Mymodule.mysub" для правильного вызова любых модулей, загружаемых добавлением.

Используя эти инструкции, мне удалось создать excel add in (.xlam файл), на котором была добавлена пользовательская вкладка, когда мой VBA загружается в Excel вместе с добавлением. Кнопки выполняют код из add и пользовательские вкладки удаляются, когда Я удаляю добавление.

Ответ 5

В дополнение к ответу Roi-Kyi Bryant этот код полностью работает в Excel 2010. Нажмите ALT + F11 и редактор VBA появится. Дважды щелкните по ThisWorkbook с левой стороны, затем вставьте этот код:

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='My Actions' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='Reports' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='runReport' label='Trim' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AppointmentColor3'      onAction='TrimSelection'/>" & 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_Deactivate()

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

Не забывайте сохранять и повторно открывать книгу. Надеюсь, это поможет!

Ответ 6

Я столкнулся с трудностями с решением Roi-Kyi Bryant, когда несколько надстроек пытались изменить ленту. У меня также нет прав администратора на моем рабочем компьютере, что исключает установку Custom UI Editor. Итак, если вы находитесь в одной лодке со мной, вот альтернативный пример настройки ленты с использованием только Excel. Обратите внимание, мое решение взято из руководства Microsoft.

Создайте файл Excel/файлы, чьи ленты вы хотите настроить. В моем случае я создал два файла .xlam, Chart Tools.xlam и Priveleged UDFs.xlam, чтобы продемонстрировать, как несколько надстроек могут взаимодействовать с лентой.
Создайте папку с любым именем папки для каждого файла, который вы только что создали.
Внутри каждой из созданных _rels папок _rels папку customUI и _rels.
Внутри каждой папки customUI создайте файл customUI.xml. Файл customUI.xml описывает, как файлы Excel взаимодействуют с лентой. Часть 2 руководства Microsoft охватывает элементы в файле customUI.xml.

Мой файл customUI.xml для Chart Tools.xlam выглядит следующим образом

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">
  <ribbon>
    <tabs>
      <tab idQ="x:chartToolsTab" label="Chart Tools">
        <group id="relativeChartMovementGroup" label="Relative Chart Movement" >
            <button id="moveChartWithRelativeLinksButton" label="Copy and Move" imageMso="ResultsPaneStartFindAndReplace" onAction="MoveChartWithRelativeLinksCallBack" visible="true" size="normal"/>
            <button id="moveChartToManySheetsWithRelativeLinksButton" label="Copy and Distribute" imageMso="OutlineDemoteToBodyText" onAction="MoveChartToManySheetsWithRelativeLinksCallBack" visible="true" size="normal"/>
        </group >
        <group id="chartDeletionGroup" label="Chart Deletion">
            <button id="deleteAllChartsInWorkbookSharingAnAddressButton" label="Delete Charts" imageMso="CancelRequest" onAction="DeleteAllChartsInWorkbookSharingAnAddressCallBack" visible="true" size="normal"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Мой файл customUI.xml для Priveleged UDFs.xlam выглядит следующим образом

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">
  <ribbon>
    <tabs>
      <tab idQ="x:privelgedUDFsTab" label="Privelged UDFs">
        <group id="privelgedUDFsGroup" label="Toggle" >
            <button id="initialisePrivelegedUDFsButton" label="Activate" imageMso="TagMarkComplete" onAction="InitialisePrivelegedUDFsCallBack" visible="true" size="normal"/>
            <button id="deInitialisePrivelegedUDFsButton" label="De-Activate" imageMso="CancelRequest" onAction="DeInitialisePrivelegedUDFsCallBack" visible="true" size="normal"/>
        </group >
      </tab>
    </tabs>
  </ribbon>
</customUI>

Для каждого файла, созданного на шаге 1, добавьте .zip к имени файла. В моем случае я переименовал Chart Tools.xlam в Chart Tools.xlam.zip, а Privelged UDFs.xlam в Priveleged UDFs.xlam.zip.
Откройте каждый файл .zip и перейдите в папку _rels. Скопируйте .rels файл в _rels папку, созданную на шаге 3. Редактировать каждый .rels файл с помощью текстового редактора. Из руководства Microsoft

Между последним элементом <Relationship> и закрывающим элементом <Relationships> добавьте строку, которая создает связь между файлом документа и файлом настройки. Убедитесь, что вы правильно указали имена папок и файлов.

<Relationship Type="http://schemas.microsoft.com/office/2006/
  relationships/ui/extensibility" Target="/customUI/customUI.xml" 
  Id="customUIRelID" />

Мой файл .rels для Chart Tools.xlam выглядит следующим образом

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        <Relationship Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="/customUI/customUI.xml" Id="chartToolsCustomUIRel" />
    </Relationships>

Мой файл .rels для Priveleged UDFs выглядит следующим образом.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        <Relationship Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="/customUI/customUI.xml" Id="privelegedUDFsCustomUIRel" />
    </Relationships>

Замените файлы .rels в каждом файле .zip на файл/файлы .rels которые вы изменили на предыдущем шаге.
Скопируйте и вставьте .customUI папку .customUI в домашний каталог .zip файла/файлов.
Удалите расширение .zip из созданных вами файлов Excel.
Если вы создали файлы .xlam, вернитесь в Excel и добавьте их в свои надстройки Excel.
Если применимо, создайте обратные вызовы в каждой из ваших надстроек. На шаге 4 в моих кнопках есть ключевые слова onAction. onAction слово onAction указывает, что при onAction содержащего элемента приложение Excel запускает подпрограмму, заключенную в кавычки, сразу после onAction слова onAction. Это называется обратным вызовом. В моих файлах .xlam меня есть модуль CallBacks который я включил свои подпрограммы обратного вызова.

Мой CallBacks модуль Chart Tools.xlam выглядит

Option Explicit

Public Sub MoveChartWithRelativeLinksCallBack(ByRef control As IRibbonControl)
  MoveChartWithRelativeLinks
End Sub

Public Sub MoveChartToManySheetsWithRelativeLinksCallBack(ByRef control As IRibbonControl)
  MoveChartToManySheetsWithRelativeLinks
End Sub

Public Sub DeleteAllChartsInWorkbookSharingAnAddressCallBack(ByRef control As IRibbonControl)
  DeleteAllChartsInWorkbookSharingAnAddress
End Sub

Мой CallBacks модуль для Priveleged UDFs.xlam выглядит

Вариант Явный

Public Sub InitialisePrivelegedUDFsCallBack(ByRef control As IRibbonControl)
  ThisWorkbook.InitialisePrivelegedUDFs
End Sub

Public Sub DeInitialisePrivelegedUDFsCallBack(ByRef control As IRibbonControl)
  ThisWorkbook.DeInitialisePrivelegedUDFs
End Sub

Различные элементы имеют различную подпись подпрограммы обратного вызова. Для кнопок обязательным параметром подпрограммы является ByRef control As IRibbonControl. Если вы не соответствуете требуемой подписи обратного вызова, вы получите сообщение об ошибке при компиляции вашего проекта/проектов VBA. Часть 3 руководства Microsoft определяет все сигнатуры обратного вызова.

Вот как выглядит мой законченный пример

Несколько заключительных советов

Если вы хотите, чтобы надстройки совместно использовали элементы ленты, используйте xlmns: слова idQ и xlmns:. В моем примере Chart Tools.xlam и Priveleged UDFs.xlam имеют доступ к элементам с idQ равным x:chartToolsTab и x:privelgedUDFsTab. Для этого требуется x: и я определил его пространство имен в первой строке моего файла customUI.xml, <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">. В разделе "Два способа настройки пользовательского интерфейса Fluent" в руководстве Microsoft приведено несколько подробностей.
Если вы хотите, чтобы надстройки обращались к элементам ленты, поставляемым с Excel, используйте ключевое слово isMSO. В разделе "Два способа настройки пользовательского интерфейса Fluent" в руководстве Microsoft приведено несколько подробностей.