2 семестр / vba_2002

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

Скрытие (с последующим отображением) всех панелей инструментов

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

Пример, приведенный в этом разделе, отображает способ скрытия всех панелей инструментов и последующего их отображения после закрытия приложения. Процедура H i d e A l l - T o o l b a r s вызывается HI процедуры обработки события workbook_Open. Процедура R e s t o r e T o o l b a r s вызывается из процедуры обработки события Workbook_Be£oreClose.

Код отслеживает панели инструментов, которые отображены на экране, сохраняя их имена в рабочем листе TBSheet. Когда рабочая книга закрывается, процедура R e s t o r e T o o l b a r s считывает список из ячеек рабочего листа и отображает необходимые панели инструментов. Использование рабочего листа для хранения имен панелей инструментов является более безопасным способом хранения, чем применение для этих же целей массива VBA (который может легко изменить свои значения). Обе процедуры приводятся в листинге 22-2.

Листинг 22.2, Скрытие всех панелей инструментов с последующим их отображением

Sub HideAllToolbarsO

Dim ТВ Аэ CommandBar

Dim TBNum As Integer

Dim TBSheet As Worksheet

Set TBSheet = Sheets{"TBSheet")

Application,ScreenUpdating = False

1Очистка листа TBSheet.Cells.Clear

1Скрыть видимые панели инструментов и сохранить их названия

TBNum = О

For Each ТВ In CommandBars

If ТВ.Type = msoBarTypeWormal Then If ТВ.Visible Then

TBNum = TBNuia + 1 ТВ.Visible = False

TBSheet.Cells(TBNum, 1) = ТВ.Name End If

End If Next ТВ

Application.ScreenUpdating = True End Sub

Sub RestoreToolbars{)

Dim TBSheet As Worksheet

Dim cell As Range

Set TBSheet = Sheets("TBSheet")

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

57$

	Application.ScreenUpdating	=	False
'	Отобразить скрытые ранее	панели инструментов
	On Error Resume Next
	For Each cell In TBSheet.Range("A:A")			_
	.SpecialCells(xlCellTypeConstants)
	CommandBars(cell.Value).Visible =			True
	Next cell
	Application.ScreenUpdating	=	True
End Sub

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

Ссылка на элементы управления командной панели

Такой объект CommandBar, как панель инструментов, содержит объекты C o n t r o l . Этн объекты, в основном, являются кнопками на панели инструментов и элементами меню.

Приведенная ниже процедура T e s t отображает значение свойства C a p t i o n для первого объекта C o n t r o l , который находится на панели инструментов Standard (Стандартная). Этот объект c o n t r o l имеет индекс 3.

Sub T e s t ( }

MsgBox CommandBars(3).Controls(l).Caption End Sub

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

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

Рис.22.7.Отображениесвойства Ca.pt ion элементауправления

Вместо номера индекса при создании ссылки на элемент управления, можно использовать значение свойства C a p t i o n . Представленная далее процедура приводит к получению такого же результата, что и предыдущая.

Sub Test2 О

MsgBox CorrjnandBars ("Standard") .Controls ( "Создать ") .Caption

End Sub

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

580	Глава 22. Создание собственных панелей инструментов

Если отображается свойство C a p t i o n элемента управления, то можно заметить, что значение данного свойства содержит символ амперсанта (&). Буква, которая следует после этого символа, задает подчеркнутую горячую клавишу в отображаемом тексте (например, &New). При ссылке на элемент управления с использованием значения свойства C a p t i o n указывать символ амперсанта не требуется.

В некоторых случаях объекты Control могут содержать другие объекты Central. Например, первый элемент управления на панели инструментов Drawing (Рисование) содержит другие элементы управления (этот пример демонстрирует способ добавления меню на панель инструментов). Концепция объектов c o n t r o l , которые содержатся внутри других объектов control, более подробно рассмотрена в главе 23, где освещается вопрос создания меню.

Перечисление элементов управления на командной панели

Следующая процедура отображает свойство C a p t i o n каждого объекта C o n t r o l , содержащего внутри объекта CommandBar. В приведенном примере используется панель инструментов Standard (Стандартная).

Sub ShowControlCaptions() Dim Cbar as CommandBar

Set CBar = CommandBars("Standard"} Cells.Clear

Row = 1

For Each ctl In CBar.Controls Cells(Row, 1) = ctl.Caption Row = Row + 1

Next ctl End Sub

Результат выполнения процедуры S h o w C o n t r o l C a p t i o n s показан на рис. 22.8.

Перечисление элементов управления на всех панелях инструментов

Отображенная далее процедура просматривает все командные панели в коллекции. Если
командная панель является панелью инструментов (т.е. ее свойство Туре установлено в зна-
чение I), то другой иикл отображает значение свойства C a p t i o n для каждой кнопки на па-
нели инструментов.
Частичный результат работы процедуры ShowAllToolbarControl s показан на рис. 22.9.
Sub ShowAllToolbarControls()
Dim row As	Integer
Dim Cbar As	CommandBar
Dim ctl As CommandBarControl
Cells.Clear
row = 1
For Each Cbar In CommandBars
If Cbar.Type = msoBarTypeNormal Then
Cells(row, 1) = Cbar.Name
For Each ctl In Cbar.Controls
ЧастьVI.Разработкаприложений		581

Cells(row, 2) = ctl.Caption row = row + 1

Next ctl End If

Next Cbar End Sub

Ямс. 22.5. Список подписей для калсдого элемента украшения,которыйнаходится на панели инструментов Standard (Стандартная)

Рис. 22.9. Списокназваний всех элементов управления на всех панеляхинструментов

Добавление элементов управления на командную панель

Для того чтобы добавить элемент упраиления в объект CommandBar, можно воспользоваться методом Add коллекции Controls. Приведенный ниже оператор добавляет элемент управления на панель инструментов MyToolbar. Свойство Туре этого элемента управления установлено в значение, равное встроенной константе msoControlButton, что приводит к созданию стандартнойкнопки.

CommandBars("MyToolbar").Controls.Add _ Type:=msoControlButton

Кнопка, добавленная на панель инструментов с помощью предыдущего оператора, не имеет функции. Щелчок на этой кнопке не приведет ни к какому результату. Как правило, большая часть свойств элемента управления устанавливается в момент добавления элемента управления на панель инструментов. Следующий код добавляет новый элемент управления, назначает ему рисунок с помощью свойства Faceld, назначает макрос посредством свойства OnAction и указывает подпись:

Sub AddButtonO

	Dim NewBtn As CommandBarButton
	Set NewBtn = CommandBars("MyToolbar").Controls.Add _
	(Type:=msoControlButton)
582	Глава 22. Создание собственных панелей инструментов

With NewBtn

.Faceld = 300

.OnAc tion = "MyMacro"

.Caption = "Экранная подсказка"

End With

End Sub

Процедура AddButton создает переменную (NewBtn), которая представляет добавленный элемент управления. После этого конструкция With-With End устанавливает свойства объекта.

Удаление элемента управления из командной панели

Для того чтобы удалить элемент управления из объекта CommandBar, воспользуйтесь методом Delete коллекции Controls. Следующий оператор удаляет первый элемент управления на панели инструментов MyToolbar; CommandBars{"MyToolbar").Controls(1).Delete

Кроме того, можно определить элемент управления, сославшись на его название. Приведенный далее оператор удаляет элемент управления, который имеет название gortButton: CommandBars("MyToalbar").Controls("SortButton").Delete

Свойства элементов управления командных панелей

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

• BeginGroup — если это свойство установить в значение True, то перед элементом управления будет отображена разделительная полоса.

• B u i l t l n — свойство имеет значение True, если элемент управления является одним из встроенных элементов управления Excel (предназначено только для чтения).

• C a p t i o n — значение этого свойства отображается в виде текста подписи элемента управления. Если элемент управления имеет только изображение, то подпись будет отображена лишь после наведения на элемент управления указателя мыши.

• E n a b l e d — если это свойство установлено в значение True, то на элементе управления можно щелкнуть.

•F a c e l d — число, которое обозначает изображение, представленное возле текста на элементе управления.

•Id — кодовый номер предопределенной команды Excel (предназначено только для чтения).

•OnAction — имя процедуры VBA, которая будет запускаться при щелчке на этом элементе управления.

•S t a t e — определяет состояние "нажатость'" элемента управления. Это свойство поддерживается только элементами управления CommandBarButton.

•S t y l e — определяет состояние отображения элемента управления с теистом и изображением. Это свойство поддерживается лишь элементами управления Command- B a r B u t t o n и CommandBarComboBox.

• T o o l T i p T e x t —• текст, отображаемый при наведении пользователем указателя мыши на элемент управления.

•Туре — целое число, которое определяет тип элемента управления.

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

583

Установка свойства Style элемента управления

Свойство s t y l e элемента управления определяет его внешний вид (это свойство относится только к элементам управления CommandBarButton и CommandBarComboBox). Значение данного свойства обычно устанавливается с помощью встроенной константы. Например, чтобы отобразить кнопку с текстом и изображением, установите свойство S t y l e в значение msoButtonlconAndCaption . Ниже представлены допустимые значения свойства S t y l e элементов управления CommandBarButtorK

•msoButtonAutomatic;

•msoButtonCaption;

•msoButtonlcon;

•msoButtonAndCaption;

•msoButtonlconAndCaptionBelow;

•msoButtonAndlconAndWrapCaption;

•msoButtonAndlconAndWrapCaptionBelow;

•msoButtonWrapCaption.

Для элемента управления CommandBarComboBox допустимыми значениями являются msoComboLabel HmsoComboNormaX.

На рис. 22.10 показана панель инструментов, на которой находится семь элементов управления (командных кнопок). Каждый элемент управления имеет свой стиль.

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

Рис. 22.10, Семь значений свойства Style для элементовуправления (кнопки)

Текст, который отображается на элементе управления, представлен значением свойства caption этого элемента управления. Отображаемый значок задается значением свойства Faceld.

Изменение значка на кнопке

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

Изображение (если оно демонстрируется), связанное с элементом управления на панели инструментов, определяется значением свойства F a c e l d . Для того чтобы значок был отображен на экране, значение свойства s t y l e элемента управления должно иметь любое значение, отличное от m s o B u t t o n C a p t i o n .

584	Глава 22. Создание собственных панелей инструментов

Приведенный ниже оператор устанавливает свойство F a c e l d первой кнопки на панели инструментов MyToolbar в значение 45, что соответствует коду изображения почтового ящика:

CommandBars ("MyToolbar") .Controls {1) .Faceld = 45

Для определения кода конкретного изображения используется метод проб и ошибок, однако вы можете воспользоваться бесплатной утилитой, которая разработана специально для отображения значекня свойства F a c e l d . Эта надстройка упрощает определение свойства F a c e l d конкретного изображения. После установки надстройки можно выбрать из меню Сервис команду Face ID Identifier. В результате выбора этой команды отображается пользовательское диалоговое окно, которое содержит все доступные значки для элементов управления командных панелей. Как только указатель мыши будет наведен на одно из этих изображений, значение свойства F a c e l d отобразится в текстовом поле (рис. 22.И).

Рис. 22.11. Надстройка FacetD Identifier предназначенаопределитьзначениясвойства Faceld для элементов управления панелейинструментов

Надстройка FacelD Identifier доступна на Web-узле издательства.

Изменение свойства Visible элемента управления

Свойство V i s i b l e определяет отображаемость на экране конкретной панели инструментов. Установка этого свойства в значение F a l s e не приведет к удалению панели инструментов. Представленная далее процедура приводит только к большому количеству изменений на экране, Для этого меняется значение v i s i b l e всех панелей инструментов, находящихся на экране. Скрытые панели отображаются, а видимые становятся скрытыми. Чтобы вернуть интерфейс к первоначальному состоянию, необходимо запустить процедуру повторно.

Sub ToggleAllToolbars() Dim cb As CommandBar On Error Resime Next

For Each cb In ComraandBars

If cb.Type = msoBarTypeNormal Then cb.Visible = Not cb.Visible

End If Next cb

End Sub

Оператор On Error Resume Next призван предотвратить появление сообщений об ошибках при попытке получить доступ к определенным панелям инструментов Ёхсе) 2002 вне корректного контекста. К панелям инструментов, доступ к которым осуществляется в пределах определенного контекста, относятся Список полей сводной таблицы, Собрание по сети и Восстановление документов.

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

Динамическое изменение подписи элемента управления

Процедура листинга 22.3 создает панель инструментов, которая содержит только одну кнопку. Подпись этой кнопки отображает тип форматирования числа для активной ячейки (рис. 22.12). Процедура использует события объекта Worksheet для определения момента изменения выделения. Когда возникаетсобытие S e l e c t i o n C h a n g e , процедура выполняется, а подпись кнопки на панели инструментов изменяется.

Рис. 22.12. Эта панель инструментов содержит кнопку с форматом числатекущейячейки

1 Листинг 22.3. Отображение текущего формата числа активной ячейки

Sub MakeNumberFormatDisplay()

Dim TBar As CommandBar

Dim NewBtn As CommandBarButton

1Удаление сушествующей панели .лнструмеятов On Error Resume Next CommandBars("Числовой формат").Delete

On Error GoTo 0

1Создание новой панели инструментов Set TBar = CommandBars.Add

With TBar

.Name = "Числовой формат"

.Visible - True End with

'Добавление элемента управления (кнопки)

Set NewBtn = CommandBars("Числовой формат").Controls.Add _ (Type:=msoControlButton)

With NewBtn
.Caption =	""
.OnAction	=	"ChangeNumFormat"
.TooItipText		= "Щелкните для изменения числового формата"
. Style - msoButtonCaption
'.Width =	100
End With
Call UpdateToolbar
End Sub
Дополнительная информация о событиях приводится в главе 19.

586 Глава 22. Создание собственных панелей инструментов

Процедура UpdateToolbar, которая отображена ниже, просто копирует свойство NumberFormat объекта ActiveCell в свойство Caption элемента управления (в данном случае — кнопки):

Sub UpdateToolbar()

On Error Resume Next CommandBars{"Числовой формат"). _

Controls(1).Caption = ActiveCell.NumberFormat

If Err <> 0 Then CommandBars("Числовой формат")- _ Controls(l).Caption = ""

End Sub

Свойство OnAction элемента управления (кнопки) указывает на процедуру Chan - geNumFormat, которая представлена ниже. Эта процедура отображает вкладку Число диалогового окна Excel Формат ячеек (рис. 22.13).

Рис. 22.13. Щелчок на кнопке ' позволяет пользователю выбратьновыйформатотображениячисел

Sub ChangeNumFormat()

Application.Dialogs(xlDialogFormatNumber).Show

Call UpdateToolbar

End Sub

Техника, описанная в этом разделе, работает достаточно неплохо, но имеет один недостаток: если пользователь изменит формат чисел с помощью кнопки на панели инструментов Formatting (Форматирование), то значение подписи кнопки на панели Числовой формат не изменится, так как подобное изменение формата чисел в активной ячейке не приводит к возникновению события, которое можно перехватить.

Назначение собственного макроса к встроенной кнопке

Каждая кнопка на встроенных панелях Excel запускает определенную процедуру. Существует возможность назначить встроенной кнопке собственный макрос. Для этого необходимо воспользоваться свойством OnAction. Следующий оператор назначает макрос кнопке Sort Ascending (Сортировка по возрастанию) на встроенной панели инструментов:

CommandBars("Standard")-Controls{"Sort Ascending") _

.OnAction = "ShowMsg"

После выполнения оператора щелчок на кнопке Sort Ascending (Сортировка по возрастанию) больше не будет приводить к привычному действию. Вместо этого вызывается процедура VBA ShowMsg.

Чтобы вернуть кнопке первоначальную функциональность, назначьте свойству OnAction

значение в виде пустой строки:
Часть VI. Разработка приложений	587

CommandBars(H Standard").Controls("Sort Ascending") _

.OnAction = ""

"Вызов" кнопки на команднойпанели

Элементы управления на командной панели имеют метод E x e c u t e . После вызова данный метод запускает внутреннюю процедуру, которая назначена встроенному элементу управления. Например, выполнение следующего оператора эквивалентно щелчку на кнопке Sort Ascending (Сортировка по возрастанию) ни панели инструментов Standard (Стандартная): C o m m a n d B a r s ( " S t a n d a r d " ) . C o n t r o l s ( " S o r t A s c e n d i n g " ) . E x e c u t e

Использование метода E x e c u t e no отношению к определенной пользователем кнопке на командной панели приводит к запуску макроса, назначенного с помощью свойства OnAction.

Использование других элементов управления командных панелей

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

Справочное руководство предоставляет описания и изображения 23-х типов элементов управления. Многие из них невозможно добавить на панели управления. В их число входят элементы упрааления, которые используются во встроенных панелях инструментов Excel. Трудно понять, зачем предоставлять описания типов элементов управления, если их нельзя использовать.

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

•m s o C o n t r o l B u t t o n — стандартная кнопка;

•m s o C o n t r o l E d i t — текстовое поле;

•msoControlComboBox — комбинированный список;

•msoControlDropdown — выпадающий список;

•m s o C o n t r o l B u t t o n P o p u p — кнопка, которая при щелчке отображает другие элементы управления. С помощью этого элемента управления можно создавать меню, состоящие из нескольких опции.

Свойство туре объекта Control предназначено только для чтения и устанавливается в момент создания элемента управления. Другими словами, изменить тип элемента управления после его создания невозможно.

Рис. 22.14. Эта панель инструментов содержит злемент управления (выпадающийсписок), ккоторомуприсоединенмакрос

Процедура M o n t h L i s t листинга 22.4 создает новую панель инструментов, добавляет на нее элемент управления в виде выпадающего списка и заполняет этот элемент управления

Глава 22. Создание собственных панелей инструментов