Зачем писать приложение «с нуля»? Ведь можно воспользоваться пакетом MS Office! Но всегда ли такое приложение полностью может удовлетворить пользователя? В данной статье предлагается рассмотреть возможности расширения стандартных контекстных меню и средств проверки вводимых данных в Excel.
В своих статьях мной неоднократно затрагивались проблемы создания панелей инструментов в MS Office, в частности в Word’е. Но если в последнем заполнение списков панелей инструментов и пользовательских форм возможно путем применения метода AddItem, обращающегося либо к информации, содержащейся в тексте программы, либо, например, к текстовому файлу, выполняющему роль базы данных, то для Excel решение во многом упрощается, т. к. электронные таблицы одновременно могут выполнять роль базы данных, пусть бы и плоской. Использование же таких мощных средств программы, как автофильтр, сводные таблицы или расчет промежуточных итогов, делают Excel’евские документы великолепными инструментами анализа данных.
Для заполнения списков ListBox и ComboBox пользовательских форм данными из таблиц совсем необязательно использование метода AddItem — достаточно на этапе разработки формы указать параметры RowSource /строки-ресурсы/ и ColumnCount /количество колонок/ для контрола (см. Рис. 1), или же прописать эти же значения в процедуре, например:
Private Sub UserForm_Initialize( ) ListBox1.ColumnCount = 5 ListBox1.RowSource = "'Значения'!a2:e45" End Sub
Где слово 'Значения' — имя листа рабочей книги, в которой находится диапазон данных a2:e45.
Использование средства «Проверка данных» (меню Данные > Проверка) позволяет задать для выделенного диапазона, в данном случае А2:А10, набор контрольных значений, диапазон которых, выделенный пользователем на рабочем листе, заносится в поле «Источник» (см. Рис. 2).
В конечном итоге, при активизации любой ячейки в диапазоне А2:А10 у правой ее границы появится кнопка выпадающего списка, содержащего значения диапазона-источника (см. Рис. 3).
Обратите внимание, контрольные значения (источник) находятся на том же рабочем листе, что и область заполнения. Они здесь явно ни к чему. Лучше перенести контрольные значения на другой лист рабочей книги Excel, а удаление ненужных значений производить, выделяя строку кликом по ее номеру (см. Рис. 4) и выбирая из контекстного меню «Удалить», причем удаление в этом случае не будет сопровождаться выбросом диалогового окна «Удаление ячеек».
Заметим, что при обработке данных (например, при фильтрации или выборке по запросу Microsoft Query) значения «Васильев» и «Васильєв», или «Сидоров», набранный полностью кириллицей, и тот же «Сидоров», имеющий заглавную «С» латинскую, будут восприниматься по-разному, а следовательно, неминуемо возникнут итоговые ошибки. Чтобы этого не произошло, можно в поле имени, слева от строки формул, задать имя диапазону контрольных значений, независимо от того, на каком листе данный диапазон находится (выделите ячейку или диапазон, введите имя в этом поле и затем нажмите клавишу ENTER), а в поле «Источник» прописать формулу = ИмяДиапазона. Но в процессе работы список контрольных значений может увеличиваться, а задавать для запаса пустые ячейки — значит позволить пользователю в формируемую базу заносить… пустоту.
Для начала переименуем листы в рабочей книге на «Значения» и «Партнеры», а контрольные значения перенесем в соответствующий лист, в ячейку А1. В дальнейшем лист «Значения» можно скрыть, т. к. обращаться к нему будут не так часто (Формат > Лист > Скрыть).
Можно создать панель инструментов или пользовательскую форму с контрольными данными, получаемыми из другого листа, но возможности, предоставляемые контекстными меню, позволяют уменьшить время редактирования документов. Поэтому предлагаю поместить в контекстное меню список значений для занесения в активную ячейку. Но если Word еще как-то позволяет редактировать контекстные меню вручную в режиме «Настройка» (см. Рис. 5), то в Excel редактировать контекстного меню можно только процедурно.
Помещенная в стандартный модуль процедура создания контекстного меню может быть следующей:
Sub KontextMnu() ' Определяем контрол — объектный тип CommandBarComboBox Dim myControl As CommandBarComboBox ' Объявление переменной для счетчика цикла заполнения списка Dim n As Long n = 0 ' Делаем контекстное меню видимым Application.CommandBars("cell").Enabled = True ' Метод Reset — переустанавливаем или перезагружаем (кому как будет удобней перевести) меню, в результате чего оно будет содержать набор стандартных команд, предусмотренных Excel. Обратите внимание, что CommandBar, над которой производятся операции, имеет специфическое имя — "cell", т. е. "ячейка" Application.CommandBars("cell").Reset ' В контекстное меню добавляем контрол, т. е. кнопку, предназначение которой — вносить текущую дату в активизируемую ячейку; впрочем, вы можете создать кнопку с любым нужным вам свойством — "OnAction" With Application.CommandBars("cell").Controls With .Add(Type:=msoControlButton, before:=1, temporary:=True) .Caption = "Дата" .OnAction = "ToDay" .FaceId = 125 .BeginGroup = True End With 'Набор инструкций для создания нового объекта - раскрывающегося списка Set myControl = Application.CommandBars("cell").Controls.Add(Type:=msoControlComboBox, before:=2, temporary:=True) With myControl 'Цикл заполнения раскрывающегося списка — выполняется до тех пор, пока ячейка в списке контрольных значений на соответствующем листе не будет пуста. Функция Trim удаляет начальные и хвостовые символы пробелов Do n = n + 1 If Sheets("Значения").Cells(n, 1).Offset(1, 0).Value = "" Then Exit Do .AddItem Trim(Sheets("Значения").Cells(n, 1).Offset(1, 0).Value) Loop ' Определение параметров раскрывающегося списка — его ширины и выполняемой им команды .DropDownWidth = 100 .OnAction = "ValueToCell" End With End With End Sub 'Определяем командные процедуры для только что созданных контролов Sub ValueToCell() ActiveCell.Value = Application.CommandBars("cell").Controls(2).Text End Sub Sub ToDay() ActiveCell.Value = Format(Date, "dd.mm.yyyy") End Sub
Теперь почти все готово. Единственное, в чем необходимо определиться, — когда осуществлять заполнение меню новыми контролами. Это имеет смысл в следующих случаях:
• в момент открытия рабочей книги;
• активизации листа «Партнеры».
Помещаем (см. Рис. 6) в объекты ThisWorkbook и Лист1(Партнеры) вызов процедуры KontextMnu.
Private Sub Workbook_Open() Call KontextMnu End Sub Private Sub Worksheet_Activate() Call KontextMnu End Sub
Что произойдет? При открытии книги контекстное меню получит новые объекты, но в случае редактирования контрольных значений придется все равно осуществлять перезагрузку контекстного меню. Поэтому при переходе с листа «Значения» на лист «Партнеры» последний будет активизирован, и раскрывающийся список обновится (см. Рис. 7).
В принципе, можно остановиться и на этом. Но в интересах эргономики контекстное меню листа «Значения» должно иметь стандартный вид. Для этого в объекте Лист2(Значения) прописываем процедуру перезагрузки контекстного меню:
Private Sub Worksheet_Activate() Application.CommandBars("cell").Reset End Sub
а в Лист1(Партнеры) добавляем процедуру реакции на райт-клик мыши:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) ' Задаем условие — если активная ячейка не относится к колонке с фамилиями листа "Партнеры", или ячейка над активной ячейкой пуста, тогда раскрывающийся список нельзя активизировать. Почему так? Колонка "А:А" выполняет роль ключевого поля в формируемой базе, и записи должны в нее заноситься последовательно, один за другим, без пропуска. Таким же образом можно распорядиться и с кнопкой помещения даты, но это уже сделайте самостоятельно If ActiveCell.Row = 1 Or ActiveCell.Column > 1 Or ActiveCell.Offset(-1, 0).Value = "" Then CommandBars("cell").Controls(2).Enabled = False Else: CommandBars("cell").Controls(2).Enabled = True End If End Sub
Далее предлагаю процедуру проверки уже введенных данных, в которой может возникнуть необходимость после корректировки контрольных значений или при наличии ошибок во время ввода данных в колонку А:А, допустим, с клавиатуры. «Прицепить» ее можно, например, к кнопке вашей панели инструментов (см. Рис. 8), созданной как вручную, так и программно. Но все же рекомендую панели инструментов создавать последним способом — информация о кнопках, созданных вручную, сохраняется в системных файлах Excel, а не в рабочих книгах, и, следовательно, кнопка будет потеряна при переносе книги на другой ПК. Плюс еще одно неудобство — панель, созданная для конкретного файла Excel, будет постоянно появляться при работе с другими книгами. Панель же, создаваемую из кода, можно запрограммировать на появление и скрытие при работе с конкретными диапазонами данных.
Sub Check_MyList() 'Переменные, используемые в циклах проверки, назначение которых проясняется при детальном рассмотрении этих самых циклов Dim Count As Integer Dim x As Long Dim y As Long ' Переменная для статусбара — пользователь должен ощущать работу приложения, а не думать, что оно у него "зависло" Dim Percents As Byte ' Переходим на лист "Партнеры" Sheets("Партнеры").Select ' Статусбар делаем видимым Application.DisplayStatusBar = True ' Свойство Enabled раскрывающегося списка устанавливаем в True, так как данные в листе будут сравниваться с данными раскрывающегося списка CommandBars("cell").Controls(2).Enabled = True ' Снимаем возможность обновления экрана с единственной целью — повысить скорость обработки (зачем тратить попусту время и ресурсы?) Application.ScreenUpdating = False ' Определяем номер последней строки, заполненной данными Range("A1").End(xlDown).Select y = ActiveCell.Row ' Цикл проверки со второй строки до последней в списке For x = 2 To y With CommandBars("cell").Controls(2) ' Вложенный цикл сравнения значения в ячейке с каждым значением списка. Если значения совпадут, устанавливаем цвет проверяемой ячейки в xlNone(т. е. нет заливки) и выходим из цикла; в противном случае ячейка с ошибочными данными будет красной (не нравится красный (3) — покрась в синий (5) :-) ) For Count = 1 To .ListCount If Trim(Cells(x, 1).Value) = .List(Count) Then Cells(x, 1).Interior.ColorIndex = xlNone Exit For Else: Cells(x, 1).Interior.ColorIndex = 3 End If Next End With 'Вычисляем примерный процент уже проверенных данных (большая точность вряд ли понадобится) Percents = (x / y) * 100 'Индикация процентов (см. Рис. 8) Application.StatusBar = "Проверено " & Percents & "%" Next 'Сигнал пользователю об окончании проверки Beep 'В результате приведения статусбара в состояние False появится сообщение "Готово" или, для нелокализованной версии, "Ready" Application.StatusBar = False ' Обновляем экран Application.ScreenUpdating = True End Sub
После окончания проверки в статусбар можно поместить свою информацию или совет, но в любом случае в дальнейшем статусбар необходимо привести в состояние False, т. к. советы Excel, отображаемые при работе, например, с ячейками или формулами, не будут отображаться. Можно воспользоваться такой процедурой:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = False End Sub
И напоследок, совет для пользователей Excel 2000 — если вас раздражает нагромождение кнопок в каждом экземпляре создаваемых новых рабочих книг, снимите флажок в окне «Параметры» (Options) (см. Рис. 9) или пропишите простенькую процедуру:
Application.ShowWindowsInTaskbar = False
На сегодня все, желаю удачи.