Зачем писать приложение «с нуля»? Ведь можно воспользоваться пакетом MS Office! Но всегда ли такое приложение полностью может удовлетворить пользователя? В данной статье предлагается рассмотреть возможности расширения стандартных контекстных меню и средств проверки вводимых данных в Excel.
В своих статьях мной неоднократно затрагивались проблемы создания панелей инструментов в MS Office, в частности в Word’е. Но если в последнем заполнение списков панелей инструментов и пользовательских форм возможно путем применения метода AddItem, обращающегося либо к информации, содержащейся в тексте программы, либо, например, к текстовому файлу, выполняющему роль базы данных, то для Excel решение во многом упрощается, т. к. электронные таблицы одновременно могут выполнять роль базы данных, пусть бы и плоской. Использование же таких мощных средств программы, как автофильтр, сводные таблицы или расчет промежуточных итогов, делают Excel’евские документы великолепными инструментами анализа данных.
Для заполнения списков ListBox и ComboBox пользовательских форм данными из таблиц совсем необязательно использование метода AddItem достаточно на этапе разработки формы указать параметры RowSource /строки-ресурсы/ и ColumnCount /количество колонок/ для контрола (см. Рис. 1), или же прописать эти же значения в процедуре, например:
Где слово 'Значения' имя листа рабочей книги, в которой находится диапазон данных a2:e45.
Использование средства «Проверка данных» (меню Данные > Проверка) позволяет задать для выделенного диапазона, в данном случае А2:А10, набор контрольных значений, диапазон которых, выделенный пользователем на рабочем листе, заносится в поле «Источник» (см. Рис. 2).
В конечном итоге, при активизации любой ячейки в диапазоне А2:А10 у правой ее границы появится кнопка выпадающего списка, содержащего значения диапазона-источника (см. Рис. 3).
Обратите внимание, контрольные значения (источник) находятся на том же рабочем листе, что и область заполнения. Они здесь явно ни к чему. Лучше перенести контрольные значения на другой лист рабочей книги Excel, а удаление ненужных значений производить, выделяя строку кликом по ее номеру (см. Рис. 4) и выбирая из контекстного меню «Удалить», причем удаление в этом случае не будет сопровождаться выбросом диалогового окна «Удаление ячеек».
Заметим, что при обработке данных (например, при фильтрации или выборке по запросу Microsoft Query) значения «Васильев» и «Васильєв», или «Сидоров», набранный полностью кириллицей, и тот же «Сидоров», имеющий заглавную «С» латинскую, будут восприниматься по-разному, а следовательно, неминуемо возникнут итоговые ошибки. Чтобы этого не произошло, можно в поле имени, слева от строки формул, задать имя диапазону контрольных значений, независимо от того, на каком листе данный диапазон находится (выделите ячейку или диапазон, введите имя в этом поле и затем нажмите клавишу ENTER), а в поле «Источник» прописать формулу = ИмяДиапазона. Но в процессе работы список контрольных значений может увеличиваться, а задавать для запаса пустые ячейки значит позволить пользователю в формируемую базу заносить… пустоту.
Для начала переименуем листы в рабочей книге на «Значения» и «Партнеры», а контрольные значения перенесем в соответствующий лист, в ячейку А1. В дальнейшем лист «Значения» можно скрыть, т. к. обращаться к нему будут не так часто (Формат > Лист > Скрыть).
Можно создать панель инструментов или пользовательскую форму с контрольными данными, получаемыми из другого листа, но возможности, предоставляемые контекстными меню, позволяют уменьшить время редактирования документов. Поэтому предлагаю поместить в контекстное меню список значений для занесения в активную ячейку. Но если Word еще как-то позволяет редактировать контекстные меню вручную в режиме «Настройка» (см. Рис. 5), то в Excel редактировать контекстного меню можно только процедурно.
Помещенная в стандартный модуль процедура создания контекстного меню может быть следующей:
Теперь почти все готово. Единственное, в чем необходимо определиться, когда осуществлять заполнение меню новыми контролами. Это имеет смысл в следующих случаях:
в момент открытия рабочей книги;
активизации листа «Партнеры».
Помещаем (см. Рис. 6) в объекты ThisWorkbook и Лист1(Партнеры) вызов процедуры KontextMnu.
Что произойдет? При открытии книги контекстное меню получит новые объекты, но в случае редактирования контрольных значений придется все равно осуществлять перезагрузку контекстного меню. Поэтому при переходе с листа «Значения» на лист «Партнеры» последний будет активизирован, и раскрывающийся список обновится (см. Рис. 7).
В принципе, можно остановиться и на этом. Но в интересах эргономики контекстное меню листа «Значения» должно иметь стандартный вид. Для этого в объекте Лист2(Значения) прописываем процедуру перезагрузки контекстного меню:
а в Лист1(Партнеры) добавляем процедуру реакции на райт-клик мыши:
Далее предлагаю процедуру проверки уже введенных данных, в которой может возникнуть необходимость после корректировки контрольных значений или при наличии ошибок во время ввода данных в колонку А:А, допустим, с клавиатуры. «Прицепить» ее можно, например, к кнопке вашей панели инструментов (см. Рис. 8), созданной как вручную, так и программно. Но все же рекомендую панели инструментов создавать последним способом информация о кнопках, созданных вручную, сохраняется в системных файлах Excel, а не в рабочих книгах, и, следовательно, кнопка будет потеряна при переносе книги на другой ПК. Плюс еще одно неудобство панель, созданная для конкретного файла Excel, будет постоянно появляться при работе с другими книгами. Панель же, создаваемую из кода, можно запрограммировать на появление и скрытие при работе с конкретными диапазонами данных.
После окончания проверки в статусбар можно поместить свою информацию или совет, но в любом случае в дальнейшем статусбар необходимо привести в состояние False, т. к. советы Excel, отображаемые при работе, например, с ячейками или формулами, не будут отображаться. Можно воспользоваться такой процедурой:
И напоследок, совет для пользователей Excel 2000 если вас раздражает нагромождение кнопок в каждом экземпляре создаваемых новых рабочих книг, снимите флажок в окне «Параметры» (Options) (см. Рис. 9) или пропишите простенькую процедуру: