Выпадающий календарь для ввода дат в Excel

Материал из Календарь событий

Перейти к:навигация, поиск

Одной из основных проблем при работе с таблицами в Excel является указание даты. Часто пользователи путаются в том, как правильно ввести дату. Нужно использовать дату в формате dd/mm/yy или же, например, mm/dd/yy? Использовать ли в качестве разделителя косую черту, тире или точки? И, всё-таки, какого же числа был третий четверг в сентябре прошлого года?

Из этой статьи вы узнаете, как создать выпадающий календарь в Word с помощью Visual Basic for Application (VBA), используя Microsoft ActiveX Calendar Control. Календарь будет создаваться в Excel 2007, но вы можете использовать и другие версии.

Код, который создает календарь и управляет им, будет помещен внутри файла Excel. Вопрос состоит в том, в какой именно файл? Если Вы хотите, чтобы выпадающий календарь был доступен всякий раз, когда Вы работаете в Excel, вам нужно поместить программный код в файл Personal.xls, иногда называемый Personal Macro Workbook. Он так называется неспроста: файл хранит скрипты, которые будут доступны вам из любого документа Excel на данном компьютере. Personal.xls открывается в скрытом режиме каждый раз, когда вы заходите в Excel. Любые макросы и функции из этого файла доступны для использования в любом другом редактируемом документе Excel.

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

Мы будем использовать файл Personal.xls, но если вы желаете поместить свой выпадающий календарь в какой-либо конкретный документ, инструкции будут аналогичными.

Запустите Excel. Далее откройте окно редактора Visual Basic, нажав на клавиши ALT+F11, либо выбрав пункт меню "Visual Basic" на вкладке "Разработчик" (по умолчанию эта вкладка скрыта от пользователей; чтобы отобразить её, в главном меню выберите "Параметры Excel"->"Основные" и поставьте галочку у пункта Показывать вкладку "Разработчик" на ленте)..

Для начала следует проверить, находится ли Personal.xls (вы можете пропустить этот шаг, если хотите сохранить свой календарь не в Personal.xls) в окне Project Explorer (обычно оно располагается в верхнем левом углу Visual Basic Editor). В случае отсутствия необходимо создать его.

Popup-cal-excel2.png

Переключитесь в Excel и выберите пункт меню в "Разработчик"->"Запись макроса".

Popup-cal-excel1.png

Когда откроется диалоговое окно "Запись макроса", выберите в выпадающем списке "Сохранить" пункт "Личная книга макросов" (Personal Macro Workbook), затем нажмите ОК, после чего нажмите на кнопку "Остановить запись" (кнопка с маленьким синим квадратом) на панели записи макросов.

Popup-cal-excel3.png

Popup-cal-excel4.png

Этими простыми действиями мы создаем копию Personal.xls.

Popup-cal-excel5.png

Переключитесь назад на Visual Basic Editor и используйте окно Project Explorer для доступа к Module1 в Personal.xls.

Popup-cal-excel6.png

Дважды щелкните на нем, чтобы открыть окно с программным кодом, где вы увидите макрос, который только что записали. Выделите строчки от Sub Macro1() до End Sub и нажмите кнопку Delete.

Popup-cal-excel7.png

Для начала вам необходимо вставить форму, на которой будет располагаться календарь. Для этого в меню "Insert" выберите пункт "UserForm". Перед вами появится пустая форма. Изменим следующие свойства нашей формы в окне Properties (как правило, оно располагается в левом нижнем углу; если его нет, отобразите его через пункт меню "View"->"Properties Window"):

Name: frmCalendar (задаёт имя формы, через которое мы будет обращаться к ней посредством VBA)

Caption: Выберите дату (задаёт название в шапке формы)

Popup-cal-excel8.png

Popup-cal-excel9.png

Добавим теперь на нашу форму календарь. Отобразите панель инструментов через пункт меню "View"->"Toolbox".

Popup-cal-excel10.png

На панели инструментов располагаются элементы, которые вы можете разместить на вашей форме. Как правило, по умолчанию, на панели инструментов отсутствует элемент для создания календаря. Если это так, выберите пункт меню "Tools"->"Additional Controls" и поставьте галочку у элемента "Календарь 12.0" ("Calendar Control 12.0"; для разных версий Word версия календаря может отличаться).

Примечание: компонент Calendar Control является ActiveX элементом (то есть файл под названием mscal.ocx), который поддерживается Microsoft Office. Обычно он устанавливается во время стандартной установки Microsoft Office Professional или Microsoft Access, так что если вы не сможете его найти в списке доступных компонентов, вам потребуется получить его из сторонних источников. Кроме того, если вы захотите использовать ваш календарь на другом компьютере, вам также нужно будет позаботиться о том, чтобы в операционной системе был файл mscal.ocx.

Теперь, когда календарь появился на панели инструментов, выберите его и нажмите на форме.

Popup-cal-excel11.png

Изменим внешний вид добавленного календаря. Для этого выделим его и в окне Properties вначале нажмём на поле Custom, а затем на кнопку […], тем самым открыв дополнительное окно. В нём уберём галочку со свойства "заголовок месяца и года", а первым днём недели поставим понедельник (вы можете изменять свойства календаря по вашему желанию). Аналогичным образом изменим другие свойства календаря:

DayFont: Размер: 8; Начертание: Обычный

GridFont: Размер: 8

Сделаем календарь и форму более компактными, изменив их размер (потянув за белые маркеры, появляющиеся при выделении календаря и формы). Чтобы увидеть , как будет выглядеть форма в о время работы, нажмите F5. Вы увидите форму UserForm именно в том виде, как она будет выглядеть в Excel. Чтобы закрыть форму и вернуться в Visual Basic Editor, нажмите на [x].

Если пользователь ошибочно откроет календарь, то его можно будет закрыть с помощью кнопки [x], избежав тем самым ввода даты. Конечно же, большинство пользователей ожидает, что форма или диалоговое окно закроются при нажатии кнопки Esc на клавиатуре. Добавим обработку клавиши Esc на нашей форме.

Щелкните по инструменту Command Button на панели инструментов, после чего щелкните по календарю (чуть левее центра) чтобы поместить кнопку на UserForm. Заметьте, что по умолчанию кнопка занимает место сзади календаря, хотя вы будете видеть её рамку.

Popup-cal-excel13.png

Установите следующие свойства у кнопки:

Name: cmdClose

Cancel: True (если данное свойство установить в true, нажатие клавиши Esc будет эквивалентно нажатию на саму кнопку)

Дважды щелкните по вашей кнопке (или нажмите клавишу "F7") и между появившихся строк добавьте Unload Me. В итоге у вас должно получиться:

Private Sub cmdClose_Click()
    Unload Me
End Sub

Рассмотрим этот код подробнее: мы добавили процедуру, которая будет вызываться при нажатии мышкой на кнопку (фактически же, так как кнопка спрятана за календарём и свойство Cancel имеет значение true, вызов будет происходить при нажатии клавиши Esc). В теле процедуры команда Unload Me приводит к закрытию нашей формы с календарём. Можете протестировать календарь и убедиться, что всё работает.

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

Создадим процедуру Calendar1_Click, которая буде выполняться каждый раз при выборе даты в календаре. Вернитесь к окну с кодом для UserForm. Откройте левый (General) выпадающий список сверху окна и выберите Calendar1. Если после этого процедура Calendar1_Click ещё не добавилась в наш код, выберите в правом выпадающем списке Click ().

Напишите в процедуре следующий код:

ActiveCell.Value = Calendar1.Value

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

Unload Me

В итоге мы получим:

Private Sub Calendar1_Click()
    ActiveCell.Value = Calendar1.Value 
    Unload Me 
End Sub


Чтобы сделать календарь немного более удобным, мы будем проверять, не содержит ли активная ячейка ранее введенную дату. Если ячейка уже содержит дату, то она будет выделена в календаре на форме. Если же ячейка будет пуста (или же содержимое ячейки будет отлично от даты), то в календаре формы будет выделена текущая дата. Чтобы сделать это, мы используем событие Initizalize формы UserForm, которое выполняется при каждом открытии формы.

Откройте левый выпадающий список в окне с кодом и выберите UserForm, а затем откройте правый список и выберите Initialize. Редактор VBA может также автоматически создать процедуру UserForm_Click. Если она вам не нужна, вы можете её стереть.

Поместите курсор между открывающими и закрывающими строчками процедуры UserForm_Initialize и введите следующее:

If IsDate(ActiveCell.Value) Then
    Calendar1.Value = DateValue(ActiveCell.Value)
Else
    Calendar1.Value = Date
End If

Данное выражение с условным оператором If проверяет активную ячейку на наличие даты. Если в ячейке находится дата, то она будет установлена текущей в календаре на форме. Если же в ячейки нет даты, то задействуется функция Date для отображения текущей даты. Результирующий код будет таким:

Private Sub UserForm_Initialize()
      If IsDate(ActiveCell.Value) Then
            Calendar1.Value = DateValue(ActiveCell.Value)
      Else
            Calendar1.Value = Date
      End If
End Sub

Теперь нужно написать код, отечающий за отображение формы с календарем по вызову пользователя. Если вы создавали файл Personal.xls, то у вас уже должен быть модуль кода (Module1). В случае если у вас его нет, выберите пункт меню "Insert" -> "Module". Дважды щелкните по названию модуля в окне Project Explorer для открытия окна с кодом (или же щелкните правой кнопкой по модулю и выберите View Code).

Напечатайте строчку Sub OpenCalendar и нажмите Enter. Visual Basic Editor автоматичеси создаст пару скобок в конце строки и добавит строчку End Sub, а курсор будет помещен между ними. Нажмите Tab и введите frmCalendar.Show. В результате должно получиться:

Sub OpenCalendar()
    frmCalendar.Show
End Sub

Теперь вы можете запускать ваш календарь прямо из Excel (не забудьте сохранить его через "File"->"Save (имя документа)"). Для этого во время работы с документом нажмите клавиши Alt+F8, либо выберите пункт меню "Макросы" на вкладке "Разработчик" (или вкладке "Вид"). Выбрав ваш макрос (PERSONAL.XLSB!OpenCalendar), нажмите "Выполнить". Если календарь работает, значит, вы всё сделали правильно.

Popup-cal-excel14.png

Добавление дополнительных возможностей

1) Добавление кнопки на панель быстрого доступа.

Полезным дополнением может стать кнопка на панели быстрого доступа (в старых версиях Excel можно также добавить новый пункт в меню), который будет запускать макрос и отображать форму с календарём. Пользователям совершенно необязательно знать, что такое макрос и что он делает - им нужно только использовать новый инструмент для вставки даты.

В главном меню выберите "Параметры Excel"->"Настройка".

Popup-cal-excel15.png

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

Popup-cal-excel16.png

Для изменения иконки нашей кнопки нажмите кнопку "Изменить" (перед этим выделив название нашего макроса в правом списке).

Popup-cal-excel17.png

Для завершения создания кнопки нажмите "Ок", после чего вы увидите её на панели быстрого доступа.

Popup-cal-excel18.png

2) Назначение клавиш быстрого вызова.

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

Назначение клавиши быстрого вызова вручную:

Нажмите клавиши Alt+F8, либо выберите пункт меню "Макросы" на вкладке "Разработчик" (или вкладке "Вид"). Выберите ваш макрос и нажмите кнопку "Параметры". Задать клавиши быстрого вызова вы можете в поле "Сочетание клавиш" (если вы хотите задать сочетание клавиш вида Ctrl+Shift+[буква], необходимо нажать клавиши Shift+[буква]).

Popup-cal-excel19.png

Назначение клавиш быстрого вызова с использованием программного кода:

В Visual Basic Editor найдите в окне Project explorer пункт "ЭтаКнига" относящийся к Personal.xls (или созданному вами документу) и дважды щелкните по нему (или же щелкните правой кнопкой мыши и выберите View Code) для открытия окна с кодом.

Popup-cal-excel20.png

Откройте левый (General) выпадающий список наверху кодового окна и выберите Workbook. Этими действиями мы создаем процедуру Workbook_Open. Она выполняется автоматически каждый раз при открытии документа. Добавим в тело процедуры строку:

Application.OnKey "+^{C}", "Module1.OpenCalendar"

Знак + соответствует клавише Shift, ^ соответствует клавише Ctrl и {C} соответствует клавише с буквой C. Таким образом, мы задаём сочетание клавиш Ctrl+Shift+C. Вы можете использовать любую ключевую комбинацию, которая вам удобнее. Программный код будет выглядеть так:

Private Sub Workbook_Open()
    Application.OnKey "+^{C}", "Module1.OpenCalendar"
End Sub

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

3) Добавление элемента в контекстное меню (по нажатию правой кнопки мыши)

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

Если вы назначили клавишу быстрого вызова как написано было написано ранее у вас уже будет процедура Workbook_Open. В противном случае создайте её (можно пропустить строчку, которая назначает клавишу быстрого вызова).

Добавьте в процедуру следующие строки:

      Dim NewControl as CommandBarControl
      Set NewControl = Application.CommandBars("Cell").Controls.Add
          With NewControl
            .Caption = "Вставить дату"
            .OnAction = "Module1.OpenCalendar"
            .BeginGroup = True
      End With

Здесь мы добавляем новый пункт в контекстное меню и устанавливаем его свойства (Caption - название меню; OnAction - процедура, которая будет выполнятся при выборе меню; BeginGroup - отделение пункта меню от остальных разделительной линией).

В результате мы получим:

Private Sub Workbook_Open()
    Application.OnKey "+^{C}", "Module1.OpenCalendar"
    Dim NewControl As CommandBarControl
    Set NewControl = Application.CommandBars("Cell").Controls.Add
    With NewControl
            .Caption = "Вставить дату"
            .OnAction = "Module1.OpenCalendar"
            .BeginGroup = True
    End With
End Sub

Для удаления пункта контекстного меню перед закрытием документа добавим процедуру Workbook_BeforeClose. Добавим в тело процедуры строчки:

On Error Resume Next
Application.CommandBars("Cell").Controls("Вставить дату").Delete

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     On Error Resume Next
    Application.CommandBars("Cell").Controls("Вставить дату ").Delete
End Sub

В случае, если у вас появилось много одинаковых пунктов меню, значит ваш код работает не правильно. Как правило, это происходит из-за того, что вы забыли добавить удаление пункта меню или указали неправильное имя, или просто сделали опечатку. В этом нет ничего страшного. Просто откройте в редакторе Visual Basic окно Immediate Window (через меню "View" или с помощью клавиш Ctrl + G). Immediate Window имеет множество применений, одним из которых является возможность выполнить строку кода, просто набрав его в окне и нажав клавишу ввода. Введите в Immediate Window строку:

Application.CommandBars("Text").Controls("Вставить дату").Delete

а затем нажмите клавишу Enter. Одна копия пункта меню должна была после этого исчезнуть. Повторите эту процедуру столько раз, сколько необходимо, чтобы удалить все нежелательные экземпляры. После чего найдите ошибку в вашем коде и исправьте её.

Источник

www.fontstuff.com

Навигация
Календари
Инструменты