Текст книги "Excel. Трюки и эффекты"

Отображение панели инструментов при определенном условии

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

В модуле рабочего листа напишем следующий код (листинг 3.89).

Листинг 3.89. Код в модуле рабочего листа

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

' Проверка условия отображения

If Union(Target, Range(«A1:D5»)).Address = _

Range(«A1:D5»).Address Then

' Условие выполнено – можно показывать панель

CommandBars(«AutoSense»).Visible = True

Else

' Условие не выполнено – панель нужно скрыть

CommandBars(«AutoSense»).Visible = False

End If

End Sub

Из содержимого данного кода видно, что условием отображения пользовательской панели инструментов на вкладке Надстройки будет выделение любой ячейки диапазона A1:D5 (либо выделение произвольного диапазона внутри данного диапазона).

Теперь необходимо написать в стандартном модуле код, содержимое которого приведено в листинге 3.90. Для начала работы с панелью необходимо запустить макрос CreatePanel.

Листинг 3.90. Код в стандартном модуле

Sub CreatePanel()

Dim cbrBar As CommandBar

Dim button As CommandBarButton

Dim i As Integer

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

On Error Resume Next

CommandBars(«AutoSense»).Delete

On Error GoTo 0

' Создание панели инструментов

Set cbrBar = CommandBars.Add

' Создание кнопок и их настройка

For i = 1 To 4

Set button = cbrBar.Controls.Add(msoControlButton)

With button

.OnAction = «ButtonClick» & i

.FaceId = i + 37

End With

Next i

cbrBar.Name = «AutoSense»

End Sub

Sub ButtonClick3()

' Перемещение вниз

On Error Resume Next

ActiveCell.Offset(1, 0).Activate

End Sub

Sub ButtonClick1()

' Перемещение вверх

On Error Resume Next

ActiveCell.Offset(-1, 0).Activate

End Sub

Sub ButtonClick2()

' Перемещение вправо

On Error Resume Next

ActiveCell.Offset(0, 1).Activate

End Sub

Sub ButtonClick4()

' Перемещение влево

On Error Resume Next

ActiveCell.Offset(0, -1).Activate

End Sub

Созданная панель инструментов называется AutoSense и включает в себя четыре кнопки со стрелками, предназначенные для перемещения курсора на одну ячейку в соответствующем направлении. Эта панель будет отображаться на вкладке Надстройки только при условии, что курсор расположен внутри диапазона A1:D5. Если курсор вывести из этого диапазона даже с помощью кнопок данной панели, то она перестанет отображаться и появится вновь только при соблюдении указанного условия.

Скрытие и отображение панелей инструментов

В данном разделе мы научимся быстро управлять отображением всех используемых в Excel 2007 панелей инструментов.

Внимание!

Для успешной реализации данного примера в текущей рабочей книге должен находиться лист Лист1. В противном случае необходимо внести соответствующие изменения в приведенный ниже код.

В стандартном модуле редактора VBA напишем следующий код (листинг 3.91).

Листинг 3.91. Управление отображением панелей инструментов

Sub HidePanels()

Dim cbrBar As CommandBar

Dim intRow As Integer ' Номер текущей строки листа

' Отключение обновления экрана

Application.ScreenUpdating = False

' Подготовка к сохранению

Cells.Clear

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

intRow = 1 ' Запись имен с первой строки

For Each cbrBar In CommandBars

If cbrBar.Type = msoBarTypeNormal Then

If cbrBar.Visible Then

cbrBar.Visible = False

Cells(intRow, 1) = cbrBar.Name

intRow = intRow + 1

End If

End If

Next

' Включение обновления экрана

Application.ScreenUpdating = True

End Sub

Sub ShowPanels()

Dim cell As Range ' Текущая ячейка листа

' Отключение обновления экрана

Application.ScreenUpdating = False

' Отображение скрытых панелей

On Error Resume Next

For Each cell In Range(«A:A»).SpecialCells( _

xlCellTypeConstants)

CommandBars(cell.Value).Visible = True

Next cell

' Включение обновления экрана

Application.ScreenUpdating = True

End Sub

После написания кода в окне выбора макросов появятся два макроса: Hide Panel s и ShowPanels. При выполнении первого макроса все используемые панели инструментов будут скрыты, а при выполнении второго они восстановятся на прежних местах. На рабочем листе Лист1 будет храниться перечень скрытых панелей. Для удобства работы можно назначить обоим макросам кнопки и поместить их на рабочий лист (но не на какую-нибудь панель инструментов).

Создание меню на основе данных рабочего листа

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

В качестве исходных данных для создания пользовательского меню используем содержимое листа ЛистМеню (рис. 3.34).

Рис. 3.34. Исходные данные

Теперь в модуле ЭтаКнига напишем такой код (листинг 3.92).

Листинг 3.92. Код в модуле ЭтаКнига

Sub Workbook_Open()

' Создание меню

Call CreateCustomMenu

End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

' Удаление меню перед закрытием книги

Call DeleteCustomMenu

End Sub

Следующий код (листинг 3.93) необходимо написать в стандартном модуле.

Листинг 3.93. Код в стандартном модуле

Sub CreateMenu()

Dim sheet As Worksheet ' Лист с описанием меню

Dim intRow As Integer ' Считываемая строка

Dim cbrpBar As CommandBarPopup ' Выпадающее меню

Dim objNewItem As Object ' Элемент меню cbrpBar

Dim objNewSubItem As Object ' Элемент подменю objNewItem

Dim intMenuLevel As Integer ' Уровень вложенности пункта меню

Dim strCaption As String ' Название пункта меню

Dim strAction As String ' Макрос пункта меню

Dim fIsDevider As Boolean ' Нужен разделитель

Dim intNextLevel As Integer ' Уровень вложенности следующего _

пункта меню

Dim strFaceID As String ' Номер значка пункта меню

' Расположение данных для меню

Set sheet = ThisWorkbook.Sheets(«ЛистМеню»)

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

Call DeleteMenu

' Данные считываем со второй строки

intRow = 2

' Добавление меню

Do Until IsEmpty(sheet.Cells(intRow, 1))

' Считываем информацию о пункте меню

With sheet

' Уровень вложенности

intMenuLevel = .Cells(intRow, 1)

' Название

strCaption = .Cells(intRow, 2)

' Название макроса для меню

strAction = .Cells(intRow, 3)

' Нужен ли разделитель перед меню?

fIsDevider = .Cells(intRow, 4)

' Номер стандартного значка (если значок нужен)

strFaceID = .Cells(intRow, 5)

' Уровень вложенности следующего меню

intNextLevel = .Cells(intRow + 1, 1)

End With

' Создаем меню в зависимости от уровня его вложенности

Select Case intMenuLevel

Case 1

' Создаем меню

Set cbrpBar = Application.CommandBars(1). _

Controls.Add(Type:=msoControlPopup, _

Before:=strAction, _

Temporary:=True)

cbrpBar.Caption = strCaption

Case 2

' Создаем элемент меню

If intNextLevel = 3 Then

' Следующий элемент вложен в создаваемый, то есть _

создаем раскрывающееся подменю

Set objNewItem = _

cbrpBar.Controls.Add(Type:=msoControlPopup)

Else

' Создаем команду меню

Set objNewItem = _

cbrpBar.Controls.Add(Type:=msoControlButton)

objNewItem.OnAction = strAction

End If

' Установка названия нового пункта меню

objNewItem.Caption = strCaption

' Установка значка нового пункта меню (если нужно)

If strFaceID <> "" Then

objNewItem.FaceId = strFaceID

End If

' Если нужно, то добавим разделитель

If fIsDevider Then

objNewItem.BeginGroup = True

End If

Case 3

' Создание элемента подменю

Set objNewSubItem = _

objNewItem.Controls.Add(Type:=msoControlButton)

' Установка его названия

objNewSubItem.Caption = strCaption

' Назначение макроса (или команды)

objNewSubItem.OnAction = strAction

' Установка значка (если нужно)

If strFaceID <> "" Then

objNewSubItem.FaceId = strFaceID

End If

' Если нужно, то добавим разделитель

If fIsDevider Then

objNewSubItem.BeginGroup = True

End If

End Select

' Переход на следующую строку таблицы

intRow = intRow + 1

Loop

End Sub

Sub DeleteMenu()

Dim sheet As Worksheet ' Лист с описанием меню

Dim intRow As Integer ' Считываемая строка

Dim strCaption As String ' Название меню

Set sheet = ThisWorkbook.Sheets(«ЛистМеню»)

' Данные начинаются со второй строки

intRow = 2

' Считываем данные, пока есть значения в столбце "A", _

и удаляем созданные ранее меню (с уровнем вложенности 1)

On Error Resume Next

Do Until IsEmpty(sheet.Cells(intRow, 1))

If sheet.Cells(intRow, 1) = 1 Then

strCaption = sheet.Cells(intRow, 2)

Application.CommandBars(1).Controls(strCaption).Delete

End If

intRow = intRow + 1

Loop

On Error GoTo 0

End Sub

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

Пользовательское меню, созданное на основании приведенных на рис. 3.34 исходных данных, показано на рис. 3.35.

Рис. 3.35. Пользовательское меню Выручка

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

Создание контекстного меню

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

Итак, создадим пользовательское контекстное меню со следующими командами: Числовой формат, Выравнивание, Шрифт, Границы, Узор и Защита. С помощью этих команд на экран будет выводиться окно форматирования ячейки (вызываемое также нажатием комбинации клавиш Ctrl+1) с соответствующей открытой вкладкой. Созданное меню будет вызываться щелчком правой кнопки мыши на любой ячейке диапазона A2:D5.

Для решения поставленной задачи необходимо написать три кода: в модуле рабочего листа, в модуле Эта Книга и в стандартном модуле.

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

Листинг 3.94. Код в модуле рабочего листа

Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, _

Cancel As Boolean)

' Проверка, попадает ли выделенная ячейка в диапазон

If Union(Target.Range(«A1»), Range(«A2:D5»)).Address = _

Range(«A2:D5»).Address Then

' Показываем свое контекстное меню

CommandBars(«MyContextMenu»).ShowPopup

Cancel = True

End If

End Sub

После этого в модуль Эта Книга необходимо поместить код, приведенный в листинге 3.95.

Листинг 3.95. Код в модуле ЭтаКнига

Sub Workbook_Open()

' Создание контекстного меню при открытии книги

Call CreateCustomContextMenu

End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)

' Удаление меню при закрытии книги

Call DeleteCustomContextMenu

End Sub

В стандартном модуле нужно написать самый большой код – его содержимое представлено в листинге 3.96.

Листинг 3.96. Код в стандартном модуле

Sub CreateCustomContextMenu()

' Удаление одноименного меню

Call DeleteCustomContextMenu

' Создание меню

With CommandBars.Add(«MyContextMenu», msoBarPopup, ,

True).Controls

' Создание и настройка кнопок меню

' Кнопка «Числовой формат»

With .Add(msoControlButton)

.Caption = «&Числовой формат...»

.OnAction = «ShowFormatNumber»

.FaceId = 1554

End With

' Кнопка «Выравнивание»

With .Add(msoControlButton)

.Caption = «&Выравнивание...»

.OnAction = «ShowFormatAlignment»

.FaceId = 217

End With

' Кнопка «Шрифт»

With .Add(msoControlButton)

.Caption = «&Шрифт...»

.OnAction = «ShowFormatFont»

.FaceId = 291

End With

' Кнопка «Границы»

With .Add(msoControlButton)

.Caption = «&Границы...»

.OnAction = «ShowFormatBorder»

.FaceId = 149

.BeginGroup = True

End With

' Кнопка «Узор»

With .Add(msoControlButton)

.Caption = «&Узор...»

.OnAction = «ShowFormatPatterns»

.FaceId = 1550

End With

' Кнопка «Зашита»

With .Add(msoControlButton)

.Caption = «&Защита...»

.OnAction = «ShowFormatProtection»

.FaceId = 2654

End With

End With

End Sub

Sub DeleteCustomContextMenu()

' Удаление меню

On Error Resume Next

CommandBars(«MyContextMenu»).Delete

End Sub

Sub ShowFormatNumber()

' Число

Application.Dialogs(xlDialogFormatNumber).Show

End Sub

Sub ShowFormatAlignment()

' Выравнивание

Application.Dialogs(xlDialogAlignment).Show

End Sub

Sub ShowFormatFont()

' Шрифт

Application.Dialogs(xlDialogFormatFont).Show

End Sub

Sub ShowFormatBorder()

' Граница

Application.Dialogs(xlDialogBorder).Show

End Sub

Sub ShowFormatPatterns()

' Вид (Узор)

Application.Dialogs(xlDialogPatterns).Show

End Sub

Sub ShowFormatProtection()

' Защита

Application.Dialogs(xlDialogCellProtection).Show

End Sub

После написания данного кода будут сформированы макросы создания пользовательского контекстного меню (CreateCustomContextMenu) и его удаления (DeleteCustomContextMenu), а также макросы, привязанные к командам созданного меню и предназначенные для вызова соответствующих вкладок диалогового окна Формат ячеек.

После выполнения макроса CreateCustomContextMenu будет сформировано контекстное меню, изображенное на рис. 3.36.

Рис. 3.36. Пользовательское контекстное меню

Это меню будет вызываться при щелчке правой кнопкой мыши на любой ячейке диапазона A2:D5. С помощью его команд осуществляется быстрый переход к соответствующей вкладке окна форматирования активной ячейки.

Просмотр содержимого папки

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

Листинг 3.97. Просмотр содержимого папки

' Объявление API-функции для отображения стандартного окна _

просмотра папок

Declare Function SHBrowseForFolder Lib «shell32.dll» _

Alias «SHBrowseForFolderA» (lpBrowseInfo As BROWSEINFO) As

Long

' Объявление API-функции для преобразования данных, возвращаемых _

функцией SHBrowseForFolder, в строку

Declare Function SHGetPathFromIDList Lib «shell32.dll» _

Alias «SHGetPathFromIDListA» (ByVal pidl As Long, ByVal _

pszPath As String) As Long

' Структура используется функцией SHBrowseForFolder

Type BROWSEINFO

hwndOwner As Long ' Родительское окно (для диалога)

pidlRoot As Long ' Корневая папка для просмотра

strDisplayName As String

strTitle As String ' Заголовок окна

ulFlags As Long ' Флаги для окна

' Следующие три параметра в VBA не используются

lpfn As Long

lParam As Long

iImage As Long

End Type

Sub BrowseFolder()

Dim strPath As String ' Папка, список файлов которой выводится

Dim strFile As String

Dim intRow As Long ' Текущая строка таблицы

' Выбор папки

strPath = dhBrowseForFolder()

If strPath = "" Then Exit Sub

If Right(strPath, 1) <> "" Then strPath = strPath & ""

' Оформление заголовка отчета

ActiveSheet.Cells.ClearContents

ActiveSheet.Cells(1, 1) = «Имя файла»

ActiveSheet.Cells(1, 2) = «Размер»

ActiveSheet.Cells(1, 3) = «Дата/время»

ActiveSheet.Range(«A1:C1»).Font.Bold = True

' Просмотр объектов в папке...

' Первый объект папки

strFile = Dir(strPath, 7)

intRow = 2

Do While strFile <> ""

' Запись в столбец "A" имени файла

ActiveSheet.Cells(intRow, 1) = strFile

' Запись в столбец "B" размера файла

ActiveSheet.Cells(intRow, 2) = FileLen(strPath & strFile)

' Запись в столбец "C" времени изменения файла

ActiveSheet.Cells(intRow, 3) = FileDateTime(strPath &

strFile)

' Следующий объект папки

strFile = Dir

intRow = intRow + 1

Loop

End Sub

Function dhBrowseForFolder() As String

Dim biBrowse As BROWSEINFO

Dim strPath As String

Dim lngResult As Long

Dim intLen As Integer

' Заполнение полей структуры BROWSEINFO

' Корневая папка – Рабочий стол

biBrowse.pidlRoot = 0&

' Заголовок окна

biBrowse.strTitle = «Выбор папки»

' Тип возвращаемой папки

biBrowse.ulFlags = &H1

' Вывод стандартного окна просмотра папок

lngResult = SHBrowseForFolder(biBrowse)

' Обработка результата работы окна

If lngResult Then

' Получение пути (по возвращенным данным)

strPath = Space$(512)

If SHGetPathFromIDList(ByVal lngResult, ByVal strPath)

Then

' Строка пути заканчивается символом Chr(0)

intLen = InStr(strPath, Chr$(0))

' Выделение и возврат пути

dhBrowseForFolder = Left(strPath, intLen – 1)

Else

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

dhBrowseForFolder = ""

End If

Else

' Пользователь нажал кнопку «Отмена»

dhBrowseForFolder = ""

End If

End Function

Особенность этой программы – использование API-функций работы с объектами файловой системы Windows – SHBrowseForFolderи SHGetPathFromlDList. Первая функция отображает стандартное диалоговое окно просмотра дерева папок и возвращает целое значение, идентифицирующее выбранную папку (или О в случае отмены выбора). Вторая функция позволяет определить путь папки, идентифицируемой этим значением.

После написания данного кода в окне выбора макросов станет доступен макрос BrowseFolder. После его выполнения откроется окно Обзор папок, в котором по обычным правилам Windows следует указать путь к требуемой папке и нажать кнопку ОК. В результате на текущем рабочем листе будет сформирован перечень файлов, входящих в состав указанной папки (рис. 3.37).

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

Рис. 3.37. Список файлов

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

Листинг 3.98. Просмотр содержимого папки с указанием полного пути к файлам

' Объявление API-функции для отображения стандартного окна _

просмотра папок

Declare Function SHBrowseForFolder Lib «shell32.dll» _

Alias «SHBrowseForFolderA» (lpBrowseInfo As BROWSEINFO) As

Long

' Объявление API-функции для преобразования данных, возвращаемых _

функцией SHBrowseForFolder, в строку

Declare Function SHGetPathFromIDList Lib «shell32.dll» _

Alias «SHGetPathFromIDListA» (ByVal pidl As Long, ByVal _

pszPath As String) As Long

' Структура используется функцией SHBrowseForFolder

Type BROWSEINFO

hwndOwner As Long ' Родительское окно (для диалога)

pidlRoot As Long ' Корневая папка для просмотра

strDisplayName As String

strTitle As String ' Заголовок окна

ulFlags As Long ' Флаги для окна

' Следующие три параметра в VBA не используются

lpfn As Long

lParam As Long

iImage As Long

End Type

Sub BrowseFolder1()

Dim strPath As String ' Папка, список файлов которой выводится

Dim strFile As String

Dim intRow As Long ' Текущая строка таблицы

' Выбор папки

strPath = dhBrowseForFolder()

If strPath = "" Then Exit Sub

If Right(strPath, 1) <> "" Then strPath = strPath & ""

' Оформление заголовка отчета

ActiveSheet.Cells.ClearContents

ActiveSheet.Cells(1, 1) = «Имя файла»

ActiveSheet.Cells(1, 2) = «Размер»

ActiveSheet.Cells(1, 3) = «Дата/время»

ActiveSheet.Range(«A1:C1»).Font.Bold = True

' Просмотр объектов в папке...

' Первый объект папки

strFile = Dir(strPath, 7)

intRow = 2

Do While strFile <> ""

' Запись в столбец "A" имени файла

ActiveSheet.Cells(intRow, 1) = strPath & strFile

' Запись в столбец "B" размера файла

ActiveSheet.Cells(intRow, 2) = FileLen(strPath & strFile)

' Запись в столбец "C" времени изменения файла

ActiveSheet.Cells(intRow, 3) = FileDateTime(strPath &

strFile)

' Следующий объект папки

strFile = Dir

intRow = intRow + 1

Loop

End Sub

Function dhBrowseForFolder() As String

Dim biBrowse As BROWSEINFO

Dim strPath As String

Dim lngResult As Long

Dim intLen As Integer

' Заполнение полей структуры BROWSEINFO

' Корневая папка – Рабочий стол

biBrowse.pidlRoot = 0&

' Заголовок окна

biBrowse.strTitle = «Выбор папки»

' Тип возвращаемой папки

biBrowse.ulFlags = &H1

' Выводим стандартное окно просмотра папок

lngResult = SHBrowseForFolder(biBrowse)

' Обработка результата работы окна

If lngResult Then

' Получение пути (по возвращенным данным)

strPath = Space$(512)

If SHGetPathFromIDList(ByVal lngResult, ByVal strPath)

Then

' Строка пути заканчивается символом Chr(0)

intLen = InStr(strPath, Chr$(0))

' Выделение и возврат пути

dhBrowseForFolder = Left(strPath, intLen – 1)

Else

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

dhBrowseForFolder = ""

End If

Else

' Пользователь нажал кнопку «Отмена» в окне

dhBrowseForFolder = ""

End If

End Function

После написания данного кода в окне выбора макросов станет доступным макрос BrowseFolderl. Результат его выполнения показан на рис. 3.38.

Рис. 3.38. Список файлов суказанием пути

На рисунке видно, что для каждой позиции данного списка указывается полный путь к файлу.