2 семестр / vba_2002

Sub DeleteMenuO

On Error Resume Next

CommandBars(1).Controls("Бюджет").Delete

End Sub

В процессе создания меню название устанавливалось с помощью следующего оператора: NewMenu.Caption = "&Бнджет"

При удалении меню использовать символ амперсанта не обязательно.

Добавление опций в меню

В примере раздела "Добавление меню в строку меню" показано, как добавить меню в строку меню. В листинге 23.2 приводится расширенный код, который демонстрирует добавление опций в новое меню.

Листинг 23.2. Добавление опций в меню Бюджет

Sub	CreateMenu()
	Dim HelpMenu As	CommandBarControl
	Dim NewMenu As	CommandBarPopup
	Dim MenuItern As	CcrnimandBarControl
	Dim Submenuitem	As CommancffiarButton
1	Удаление меню, если таковое существует
	Call DeleteMenu
'	Поиск меню Справка
	Set HelpMenu = CommandBars(1).FindControl{Id:=30010)
	If HelpMenu Is Nothing Then
1	Добавление меню в конец строки меню
	Set NewMenu = CommandBars(1).Controls.Add _
	(Type:=msoControlPopup,		_
	temporary:=True)
	Else
1	Добавление меню перед меню Справка
	Set NewMenu = CommandBars(1).Controls.Add _
	(Type:=msoControlPopup,		_
	Before:=HelpMenu.Index,		_
	temporary:=True)
	End If
'	Добавление подписи
	NewMenu.Capt ion = "^Бюджет"
1	Первый элемент	меню
	Set Menultem = NewMenu.Controls-Add _
	(Type:=msoControlButton)
	With Menultem
	.Caption = "^Введение данных..."
	.Faceld = 162
	.OnAction =	"Macrol"
	End With

1Второй элемент меню

Set Menultem = NewMenu-Controls.Add _ (Type:=msoControlButton)

Часть V/. Разработка приложений

599

With Menultem

.Caption = "^Генерация отчета..."

.Faceld = 590

.OnAction = 'Macro2" End With

Третий элемент меню

Set Menultem = NewMenu.Controls.Add _ (Type:=msoControlPopup)

With Menultem

.Caption = "Просмотр ^диаграмм" ,BeginGroup = True

End With

1Первый элемент подменю

Set Submenuitem = Menultem.Controls-Add _ (Type:=msoControlButton)

Wi th Submenui tern

,Caption = "Ежемесячное изменение"

.Faceld = 420

.OnAction = "МасгоЗ" End With

1Второй элемент подменю

Set Submenuitem = Menultem.Controls.Add _

(Type:=msoControlBut ton) With Submenuitem

.Caption = "Отчет за &год"

.Faceld = 4 2 2

.OnAction = "Macro4" End With

End Sub

Процедура CreateMenu создаст меню, показанное на рис. 23.2. Это меню имеет три опции, причем последняяопция меню представляетсобойподменю сдвумяопциями.

Рис.23.2.ПроцедураVBAсоздаст-jmoменю,атакжесвязанныесни.иопциименю

У вас может возникнуть вопрос, зачем в коде предыдущего примера удалять уже существующее меню— ведь можно завершить выполнение приложения. Воссоздание меню гарантирует использование последней версии меню в строке меню. Кроме того, при разработке приложения таким образом уменьшается количество ручной работы (разработчик освобождается от удаления созданного меню). Процедурасозданияменюзанимаетнемноговремени,поэтомувременем,потраченным на воссозданиеменю, можнопренебречь.

Глава23.Созданиепользовательскихменю

При рассмотрении процедура CreateMenu помните о следующем.

•Первые две опции меню имеют тип m s o C o n t r o l B u t t o n , соответствующий командной кнопке. Третья опция меню имеет тип msoControlPopup, поскольку содержит собственные опции. Таким образом, переменная Menultem объявлена как имеющая универсальный тип CommanaBarControl.

•Свойство BeginGroup третьей опции меню имеет значение True, что обеспечивает отображение разделительной полосы. Разделительная полоса выступает исключительно косметическим элементом управления и служит для группировки функционально близких опций меню.

•Свойство FacelD определяет значок (если он необходим), который будет отображаться возле подписи опции меню. Значение свойства FacelD определяет изображение значка.

•Текст, задающий значение свойства C a p t i o n , содержит знак амперсанта (&) для указания символа "горячей" клавиши данной опции меню. Этот символ отображается подчеркнутым и предоставляет доступ к опции меню с помощью клавиатуры.

Добавление опции в меню Сервис

Пример в листинге 23.2 добавляет несколько опции в пользовательское меню строки меню листа. Часто возникает необходимость в добавлении опций в одно из встроенных меню Excel, например, меню Сервис.

В Excel 5 и Excel 95 назначить макрос новой опции меню Сервис было достаточно просто. По определенным причинам эта возможность отключена* начиная с Excel 97. В данном разделе продемонстрирован метод создания кода VBA, который добавляет опцию во встроенное меню Excel — Сервис.

Листинг 23.3 содержит код добавления опции меню Очистить все кроме формул в меню Сервис. Щелчок ка этой опции меню запускает процедуру C l e a r A l l B u t F o r m u i a s .

Листинг 23.3. Добавление опции в меню Сервис

Sub AddMenultem()

Dim ToolsMenu As CommandBarPopup

Dim NewMenuItem As CommandBarButton

' Удаление элемента меню, если он существует Call DeleteMenuItem

1Поиск меню Сервис

Set ToolsMenu = CommandBars(1).FindControl(Id:=30Q07) If ToolsMenu is Nothing Then

MsgBox "Невозможно добавить элемент." Exit Sub

Else

Set NewMenuItem = ToolsMenu.Controls.Add _

(Type:=msoControlButton)

With NewMenuItem.

.Caption = •Очистить в&се кроме формул"

.Faceld = 348

.OnAction я "ClearAllButFormulas"

.BeginGroup = True

End With

End If

End Sub

ЧастьVI.Разработкаприложений

601

На рис. 23.3 показано меню Сервис с новой опцией. Обратите внимание, что код не ссылается на меню Сервис по названию. Вместо этого данное меню определяется с помощью свойства ID (которое имеет значение 30007).

Рис. 23.3. Вменю Сервис добавлена новая опция

Удаление опции из менюСервис

Для того чтобы удалить опцию меню, необходимо воспользоваться методом D e l e t e коллекции C o n t r o l s . В следующем примере удаляется опция Удалить все кроме формул меню Сервис. Обратите внимание, что метод F i n d C o n t r o l используется для того, чтобы обработать ситуации, когда меню Сервис имеет другое название.

Отображение комбинации клавиш вместе с опцией меню

Некоторым встроенным опциям меню Excel назначаются определенные комбинации клавиш. Например, меню Правка содержит несколько опций с комбинациями клавиш.

Для того чтобы отобразить комбинацию клавиш вохЕе опции меню, необходимо воспользоваться свойством S h o r t c u t T e x t . Важно понимать, что установка свойства S h o r t c u t Text не приводит к назначению комбинации клавиш. Установка этого свойства просто отобразит назначенную комбинацию клавиш в опции меню. Чтобы фактически назначить комбинацию клавиш определенной команде, необходимо сгенерировать дополнительный код VBА.

Код листинга 23.4 создает опцию Очистить все кроме формул меню Сервис. Кроме того, значение свойства S h o r t c u t T e x t этой опции меню устанавливается равным " < C t r l + s h i f t+C> ". В коде также используется метод M a c r o O p t i o n s , который позволяет назначить указанную комбинацию клавиш.

Листинг23.4.Добавлениеопциименюскомбинациейклавиш

Sub AddMenultem()

Dim ToolsMenu As CommandBarPopup

Dim NewMenuItem As	CommandBarButton
602	Глава23.Созданиепользовательскихменю

' Удаление элемента меню, если он существует Call DeleteMenuItem

Поиск меню Сервис

Set ToolsMenu я CommandBars(1).FindControl(Id:=30007} If ToolsMenu Is Nothing then

	MsgBox "Невозможно добавить			элемент - используйте _
				Ctrl+Shift+C."
	Exit Sub
	Else
	Set NewMenuItem = ToolsMenu.Controls.Add __
	(Type:=msoControlButton)
	With NewMenuItem
	.Caption = "Очистить в&се кроме формул"
	.Faceld = 348
	.ShortcutText		= "Ctrl+Shift+C"
	.OnAction =	"ClearAllButFormulas"
	.BeginGroup	=	True
	End With
	End If
1	Назначение комбинации		клавиш
	Application.MacroOptions _
	Macro:="ClearAllButFormulas",			_
	HasShortcutKey:=True, _
	ShortcutKey:="C"

End Sub

После выполнения этой процедуры опция меню отображается так, как показано на рис. 23.4.

Рис. 23.4. Опция меню Очистить все, кроме формул содержит комбинацию клавиш

Часть VI. Разработка приложений

ваз

Исправление восстановленного меню

Представим себе такой сценарий развития событий: написан код VBA, создающий меню при открытии рабочей книги приложения. Пользователь открывает новую рабочую книгу, которая содержит макрос, восстанавливающий состояние строки меню. Можно также рассмотреть следующий вариант." пользователь работает с диалоговым окном Нзстройка, выбирает опцию Строка меню листа в списке этого диалогового окна и шелкает на кнопке Сброс. В обоих случаях добавленное меню будет удалено.

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

Очевидно, что приложения, восстанавливающие состояние строки меню Excel, достаточно распространены. Пользователи пакета Power Utility Pak часто замечают, что меню PUP 2000 исчезает без всякой причины. Обычно это происходит по причине того, что другое приложение восстанавливает строку меню листа. В связи с этим в пакет добавлена комбинация клавиш <CtrJ+Sniff+U>, которая призвана добавлять в строку меню PUP 2000. При выполнении представленного оператора процедура CreateMerm связывается с комбинацией клавиш <Ctrl+Shift+U>:

Application.MacroOptions Macro:="CreateMenu", _ HasShortcutKey:=True, ShortcuCKey:="U"

Работа с событиями

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

Примеры,приведенныевэтомразделе,демонстрируютразличныеспособыпрограммированияменю, которые используютсясовместноссобытиями.

Программированиесобытий подробнорассматривалосьвглаве19.

Автоматическое добавление и удаление меню

Если необходимо, чтобы меню добавлялось при открытии рабочей книги, то следует воспользоваться событием Open объекта Workbook. Следующий код, который находится в модуле кода объекта ЭтаКнига, запускает процедуру CreateMenu.

Privat e Sub Workbook_Open{} Call CreateMenu

End Sub

Для того чтобы удалить меню при закрытии рабочей книги, воспользуйтесь процедурой, которая приведена ниже. Эта процедура выполняется перед закрытием рабочей книги, она вызывает процедуру DeleteMenu.

604	Глава 23. Создание пользовательских меню

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu

End Sub

Но в данном случае может возникнуть проблема, если при закрытии рабочая книга не со-

храняется. Окно с сообщением Сохранить	изменения . . . ? отображается уже после того,
как возникнет событие B e f o r e C l o s e , а	процедура Workbook _ BeforeClose завершит
свою работу. Поэтому, если пользователь щелкнет на кнопке Отмена, рабочая книга оста-
нется открытой, а меню уже будет удалено!

Одним из решений этой проблемы является обход сообщения Excel и создание собственного кода, который выдает запрос на сохранение изменений в рабочей книге. Этот код должен располагаться в процедуре Workbook_Bef o r e C l o s e .

Private Sub Workbook_BeforeClose(Cancel As Boolean) If Not Me.Saved Then

Msg = "Сохранить изменения в" Msg = Msg & Me,Name & "?"

Ans = MsgBox(Msg, vbQuestion -t- vbYesNoCancel) Select Case Ans

Case vbYes Me.Save

Case vbWo

Me .Saved = True Case vbCancel

Cancel = True Exit Sub

End Select End If

Call DeleteMenu End Sub

Представленная процедура определяет, была ли сохранена рабочая книга. Если это так, го никаких проблем не возникает. Выполняется процедура DeleteMenu, и рабочая книга закрывается. Но если рабочая книга не была сохранена, то процедура отображает окно сообщения, которое дублирует окно сообщения, по умолчанию отображаемое Excel. Если пользователь щелкает на кнопке Да, то рабочая книга сохраняется, меню удаляется, а рабочая книга закрывается. Если пользователь щелкает на кнопке Нет, то свойство Saved рабочей книги устанавливается в значение True (но фактически изменения в рабочей книге не сохраняются) и меню удаляется. Если пользователь щелкает на кнопке Отмена, то событие B e f o r e C l o s e отменяется, а процедура завершается без удаления меню.

Отключение или скрытие меню

Когда меню или опиия меню отключены, то соответствующая подпись будет представле-
на светло-серым цветом. Щелчок на таком элементе меню ни к чему не приводит. Excel от-
ключает опции меню, если они неприменимы в текущем контексте. Например, опция Связи
меню Правка отключена, если активная рабочая книга не содержит связей.
Можно создать код VBA, который будет включать или отключать встроенные и поль-
зовательские меню, а также опции меню. Точно так же можно создать код, который скры-
вает меню или опции меню. Ключевым моментом в создании такого кода является исполь-
зование правильного события.
Следующая процедура сохраняется в модуле кода объекта ЭтаКнига.
P r i v a t e Sub Workbook_OpenО
C a l l AddMenu
ЧастьVI. Разработкаприложений	60S

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu

End Sub

Private Sub Workbook_Activate()

Call UnhideMenu

End Sub

Private Sub Workbook_Deactivate()

Call HideMenu

End Sub

Когда открывается рабочая книга, вызывается процедура AddMenu. Если рабочая книга закрывается, вызывается процедура DeleteMenu . Две дополнительные процедуры обработки событии выполняются, когда рабочая книга активизируется или деактивизкруется. Процедура UnhideMenu вызывается, когда рабочая книга активизируется, а процедура HideMenu вызывается в том случае, если рабочая книга деактнвизируется.

Процедура HideMenu устанавливает значение свойства V i s i b l e опции меню равным F a l s e , что приводит к его скрытию. Процедура UnhideMenu выполняет противоположную функцию. Общим результатом является отображение опции меню только тогда, когда рабочая книга является активной. Процедуры, в которых предполагается, что свойство C a p t i o n меню равно Бюджет, приводятся ниже.

Sub UnhideMenuО

	CommandBars (1} . C o n t r o l s ("Б:оджэт") . V i s i b l e = True
End	Sub
Sub	HideMenu()
	C o m m a n d B a r s ( 1 ) . C o n t r o l s ( " Б ю д ж е т " ) . V i s i b l e = F a l s e
End	Sub

Для того чтобы отключить меню, а не скрыть его, необходимо вместо свойства V i s i b l e изменить значение свойства Enabled .

Данный пример находится на Web-узле издательства.

Работа с установленными опциями меню

Некоторые встроенные опции меню Excel могут отображаться с установленным флажком или без него. Например, опция меню Вид^Строка формул имеет установленный флажок, если панель формул отображается на экране. Вели панель формул скрыта, то флажок возле этой опции меню отсутствует. При выборе данной опции состояние панели формул изменяется, и флажок выставляется.

Такую функциональность можно добавить и к пользовательским опциям меню. На рис. 23.5 показана опция меню, которая имеет флажок только тогда, когда активный лист содержит линии сетки. Выбор этой опции меню изменяет состояние отображения линий разметки, а также состояние флажка напротив опции меню. Присутствие флажка зависит от значения свойства S t a t e элемента управления опции меню.

606	Глава 23. Создание пользовательских меню

Врассматриваемомслучаеосновнойзадачейявляетсясинхронизация состоянияфлажка

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

Добавление опции меню

Процедура AddMenuItem, показанная в листинге 23.5, выполняется при каждом открытии рабочей книги. Эта процедура создает новую опцию Линии сетки в меню Вид.

Рис. 23.5. Опция меню Линии сетки имеет установленный флажок, если активный лист содержит линиисетки

Листинг 23.5. Внесение изменений во встроенное меню Excel

Dim AppObject As New XLHandler

Sub AddMenuItem()

Dim ViewMenu As CommandBarPopup

Dim NewMenuItem As CommandBarButton

'Удаление опции, если таковая существует Call DeleteMenuItem

'Поиск меню Вид

	Set ViewMenu = CommandBars(1)-FindControl(ID:=30004)
	If ViewMenu Is Nothing Then
	MsgBox "Невозможно добавить элемент меню."
	Exit Sub
	Else
	Set NewMenuItem = ViewMenu.Controls-Add _
	(Type:=msoControlButton)
	With HewMenuItem
	.Caption	=	"&Линии сетки"
	.OnAction	=	"ToggleGridlines"
	End With
	End If
1	Обработчик события
Часть VI. Разработка приложений				607

Set AppObject.AppEvents = Application

End Sub

Процедура AddMenuItem добавляет новую опцию в строку меню листа, а не в строку меню диаграммы. По этой причине новая опция отображается только тогда, когда активен рабочий лист (что является обязательным требованием).

Обратите внимание, что в последнем операторе процедура AddMenuItem настраивает события уровня приложения, которые будут обрабатываться в приложении. Процедуры обработки событий, которые хранятся в модуле класса XLHandler, приведены ниже.

Public WithEvents AppEvents As Application

Private Sub AppEvents_SheetActivat.e{ByVal Sh As Object)

Call CheckGridlines

End Sub

Private Sub AppEvents_Work£>ookActivate (EyVal Wb As Excel .Workbook)

Call CheckGridlines

End Sub

Private Sub AppEvents_WindowActivette _

(ByVal Wb As Workbook, ByVal Wn As Window}

Call CheckGridlines

End Sub

Представленная процедура имеет один недостаток: изменение состояния отображения линий сетки с помощью диалогового окна Параметры не отслеживается.

Отображение линий сетки

Главным эффектом является запуск процедуры CheckGridlines при активизации пользователем другой рабочей книги или другого рабочего листа. Эта процедура обеспечивает состояние флажка для огсцли Линии сетки мелю Вид в соответствии с параметрами активного листа.

Sub CheckGridlines()

Dim TG As CommandBarButton On Error Resume Next

Set TG = CommandBars(1).FindControl(ID:=30004). _ Controls(ЧЛинии сетки")

If ActiveWindow.DisplayGridlines Then TG.State = msoButtonDown

Else

TG.State = msoButtonUp End If

End Sub

Данная процедура проверяет характеристики активного окна и устанавливает соответствующее значение свойства S t a t e опции меню. Если линии сетки отображаются, то к опции Линии сетки добавляется флажок. Если линии сетки не отображаются, то флажок из соответствующей опции меню удаляется.

Синхронизация меню с активным листом

Если выбрана опция меню, значение свойства OnAction этой опции изменяется, что приводит к вызову процедуры ToggleGr i d l i n e s , которая показана ниже.

608	Глава 23. Создание пользовательских меню