Алексей Гладкий - Excel. Трюки и эффекты

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

Итак, добавим на вкладку Надстройки пользовательскую команду Очистить все, кроме формул. Кроме того, для удобства работы назначим данной команде сочетание клавиш Ctrl+Shift+C.

Для реализации поставленной задачи необходимо в стандартном модуле редактора VBA написать код, который выглядит следующим образом (листинг 3.103).

Sub AddMenuItem()

Dim cbrpMenu As CommandBarPopup

' Удаление аналогичной команды (при ее наличии)

Call DeleteMenuItem

' Получение доступа к меню «Сервис»

Set cbrpMenu = CommandBars(1).FindControl(ID:=30007)

If cbrpMenu Is Nothing Then

' Не удалось получить доступ

MsgBox «Невозможно добавить элемент.»

Exit Sub

Else

' Добавление новой команды в меню

With cbrpMenu.Controls.Add(Type:=msoControlButton)

' Название команды

.Caption = «Очистить в&се, кроме формул»

' Значок

.FaceId = 348

' Сочетание клавиш (только надпись на кнопке)

.ShortcutText = «Ctrl+Shift+C»

' Сопоставленный макрос

.OnAction = «ExecuteCommand»

' Добавление разделителя перед командой

.BeginGroup = True

End With

End If

' Сопоставление с макросом сочетания клавиш Ctrl+Shift+C

Application.MacroOptions _

Macro:="ExecuteCommand", _

HasShortcutKey:=True, _

ShortcutKey:="C"

End Sub

Sub ExecuteCommand()

' Очистка содержимого всех ячеек (кроме формул)

On Error Resume Next

Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents

End Sub

Sub DeleteMenuItem()

' Удаление команды из меню

On Error Resume Next

CommandBars(1).FindControl(ID:=30007). _

Controls(«Очистить в&се, кроме формул»).Delete

End Sub

В результате написания кода будут созданы три макроса: AddMenuItem (добавление команды Очистить все, кроме формул на вкладку Надстройки), DeleteMenultem (удаление созданной команды) и ExecuteCommand (макрос запускается при выполнении команды).

Новая команда на вкладке Надстройки показана на рис. 3.41.

При выполнении данной команды из ячеек текущего рабочего листа будет удалена вся информация, за исключением формул. Следует отметить, что данную операцию можно выполнять и без помощи команды Очистить все, кроме формул – для этого достаточно запустить макрос ExecuteCommand.

Рис. 3.41. Новая команда на вкладке Надстройки

Чтобы удалить команды Очистить все, кроме формул, нужно выполнить макрос DeleteMenuItem. Можно также щелкнуть на ней правой кнопкой мыши и в открывшемся контекстном меню выбрать команду Удалить настраиваемую команду.

Добавим на вкладку Надстройки пользовательскую команду Линии сетки. С помощью данной команды можно будет управлять отображением сетки на текущем рабочем листе.

Итак, в стандартном модуле редактора VBA напишем код, который представлен в листинге 3.104.

Dim AppObject As New Class1

Sub AddCommand()

Dim cbrpBar As CommandBarPopup

' Удаление аналогичной команды (при ее наличии)

Call DeleteCommand

' Получение доступа к меню «Вид»

Set cbrpBar = CommandBars(1).FindControl(ID:=30004)

If cbrpBar Is Nothing Then

' Не удалось получить доступ к меню

MsgBox «Невозможно добавить элемент меню.»

Exit Sub

Else

' Добавление команды

With cbrpBar.Controls.Add(Type:=msoControlButton)

.Caption = «&Линии сетки»

.OnAction = «GhangeGridlinesState»

End With

End If

' Даем объекту AppObject обрабатывать события

Set AppObject.AppEvents = Application

End Sub

Sub DeleteCommand()

' Удаление каманды из меню (если она там есть)

On Error Resume Next

CommandBars(1).FindControl(ID:=30004). _

Controls(«&Линии сетки»).Delete

End Sub

Sub GhangeGridlinesState()

' Изменение состояния отображения линий сетки _

на противоположное (если нет – покажем, если есть – скроем)

If TypeName(ActiveSheet) = «Worksheet» Then

ActiveWindow.DisplayGridlines = _

Not ActiveWindow.DisplayGridlines

' Установка или снятие флажка в меню

Call CheckGridlines

End If

End Sub

Sub CheckGridlines()

Dim button As CommandBarButton

On Error Resume Next

' Поиск команды «Линии сетки» в меню «Вид»

Set button = CommandBars(1).FindControl(ID:=30004). _

Controls(«&Линии сетки»)

' Изменение состояния флажка на противоположное

If ActiveWindow.DisplayGridlines Then

' Установка

button.State = msoButtonDown