Запуск макросов с помощью командной кнопки в форме

21. Создайте электронную форму для ввода данных в таблицу сведений о студентах. Форма должна содержать:

- заголовок «Сведения о студенте»;

- поле для ввода фамилии с инициалами;

- поле со списком для выбора номера группы;

- список для выбора наименования специальности;

- 2 переключателя для выбора пола;

- счетчик для выбора года рождения (1990—2010);

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

Для этого выполните следующие действия:

· переименуйте один из листов книги Excel в «Формы»;

· разместите на листе «Форма» в ячейках А30:А39 список номеров 10 групп, например, 8271-8280. Разместите в ячейках С30-С39 список названий специальностей;

· введите в ячейку D2 заголовок формы: “Сведения о студенте”. Введите в ячейки В4, В5, В7, В12, В15 следующие названия: ФИО, Группа, Специальность, Пол, Год рождения;

· в ячейку D4 введите фамилию;

· на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Поле со списком иочертите прямоугольный контур в области ячейки F5;

· щелкнув правой клавишей мыши по элементу Поле со списком, вызовите контекстное меню. Выберите пункт Формат объекта;

· установите вкладку Элемент управления. Щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с номерами групп. Разверните вкладку. Щелкните по кнопке сворачивания в поле Связь с ячейкой, затем щелкните по ячейке H5 и разверните вкладку. В поле Количество строк введите значение 5. Включите флажок Объемное затемнение, нажмите ОК;

· убедитесь в возможности выбора номера группы из списка с полем и изменении порядкового номера в ячейке H5;

· введите в ячейку D5 формулу для расшифровки порядкового номера группы в списке: =ИНДЕКС($А$30:$А$39;$Н$5). Используйте вариант функции со ссылкой. Убедитесь в правильности вывода номера группы в ячейке D5;

· на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Список иочертите прямоугольный контур в области ячеек G7:I10. Вызовите контекстное меню элемента Список и выберите пункт Формат объекта;

· щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с названиями специальностей. Разверните вкладку. Включите флажок выбора только одинарного значения, затем щелкните по кнопке сворачивания в поле Связь с ячейкой и введите адрес ячейки щелчком по кнопке K7. Разверните вкладку и включите флажок Объемное затемнение. Нажмите ОК;

· убедитесь в возможности выбора названия специальности из списка и изменении порядкового номера в ячейке К7;

· введите в ячейку D7 формулу для расшифровки порядкового номера группы в списке: =ИНДЕКС($С$30:$С$39;$K$7). Убедитесь в правильности названия специальности в ячейке D7;

· на вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Переключатель иочертите прямоугольный контур в области ячейки F12. Вызовите контекстное меню элемента Переключатель и выберите пункт Формат объекта;

· на вкладке Элемент управления щелчком по ячейке D12 введите в поле Связь с ячейкой ее абсолютный адрес и включите флажок Значение установлен. Замените название флажка на «М»;

· аналогично расположите значок переключателя в области ячейки F13 и замените его название на «Ж», при этом повторного связывания с ячейкой не требуется;

· в разделе Элементы управления формы выберите элемент Счетчик иочертите прямоугольный контур в области ячеек F15:F16. Вызовите контекстное меню элемента Счетчик и выберите пункт Формат объекта;

· на вкладке Элемент управления введите в поле Текущее значение: 1990. Введите в поле Минимальное значение: 1990. Введите в поле Максимальное значение: 2010. Введите в поле Шаг изменения: 1. Введите в поле Связь с ячейкой абсолютный адрес ячейки D15, нажмите ОК;

· проверьте работу счетчика;

· в разделе Элементы управления формы выберите элемент Кнопка иочертите прямоугольный контур в области ячеек C18:D18. Появится окно Назначить макрос объекту. Закройте окно, не назначая макрос. Замените название кнопки на «Запись в таблицу».

22. Создайте на новом листе с именем Список студентов во 2-ой строке шапку таблицы с названиями столбцов: ФИО, Группа, Специальность, Пол, Год рождения. Отрегулируйте ширину столбцов.

23. На листе Форма в ячейки B25, С25, D25, E25, F25 вставьте формулы, ссылающиеся на ячейки D4, D5, D7, D12 и D15. Проверьте формулы в ячейках B25:F25:

В ячейке В25 должна быть формула: =$D$4

В ячейке С25 должна быть формула: = ИНДЕКС($A$30:$A$39;$H$5)

В ячейке D25 должна быть формула: =ИНДЕКС($C$30:$C$39;$K$7)

В ячейке Е25 должна быть формула: =$D$12

В ячейке F25 должна быть формула: =$D$15

24. Осуществите запись начального макроса макрорекордером. Для этого:

· на вкладке Разработчик в группе Код нажмите кнопку Запись макроса;

· в поле Имя макроса введите имя макроса(по умолчанию);

· для начала записи макроса нажмите кнопку ОК;

· на листе Форма выделите ячейки B25:F25;

· на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать;

· перейдите на лист Список студентов и выделите ячейку А3;

· на вкладке Главная в группе Буфер обмена раскройте список Вставить и выберите команду Вставить з начения;

· на вкладке Разработчик в группе Код нажмите кнопку Остановить запись;

25. Проверьте работу созданного макроса. Для этого на листе «Список студентов» очистите диапазон ячеек А3:Е3, перейдите на лист «Формы», на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Выполнить. Строка сведений будет вставлена на то же место.

26. Для того чтобы новые сведения вставлялись в таблицу в следующие по порядку строки, необходимо откорректировать текст макроса. Для этого на вкладке Разработчик в группе Код нажмите кнопку Макросы, в диалоговом окне Макрос выделите имя созданного макроса и нажмите кнопку Изменить. Откроется окно редактора Visual Basic.

27. В окне редактора Visual Basic внесите изменения в текст программы после строки Sheets("Список студентов").Select

При этом должны быть следующие строки:

Sheets("Список студентов").Select

Range("A2").Select

If Cells(3, 1).Value <> "" Then

Cells(2, 1).Select

ActiveCell.End(xlDown).Cells(2).Select

Else

Range("A3").Select

End If

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

End Sub

28. Закройте окно редактора, щелкнув по самому левому значку на инструментальной панели редактора с изображением логотипа Excel. Повторно выполните макрос.

29. Назначьте кнопке «Запись в таблицу» созданный макрос. Для этого выделите кнопку правой клавишей мыши, в контекстном меню выберите пункт Назначить макрос, в окне Назначить макрос объекту выделите соответствующий макрос и нажмите ОК.

30. Выполните макрос щелчком по кнопке.

31. С помощью созданного макроса заполните список студентов данными о принятых в университет студентах (10-15 человек).

32. Используя созданный в предыдущем задании список студентов, создайте на новом листе с именем «Справка» автоматизированную форму для выдачи справки студенту следующего образца:

СПРАВКА Настоящая справка выдана студенту_____________ _____ года рождения в том, что он (она) в настоящее время учится в Санкт-Петербург­ском государственном инженерно-экономи­чес­ком университете в группе _____ по специальности ____________   Ректор университета Иванов А.В.   Дата выдачи 24 ноября 2014 г.

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

Для этого выполните следующие действия:

· Разместите на листе «Справка» в ячейках A1:G10 постоянный текст справки так, чтобы для ввода фамилии использовалась ячейка D4, для ввода года рождения – E4, для ввода № группы – В7, наименования специальности – D7.

· На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем в разделе Элементы управления формы выберите элемент Поле со списком и очертите указателем мыши прямоугольный контур в зоне ячеек A1:В2. Вызовите контекстное меню элемента Поле со списком и выберите пункт Формат объекта;

· Установите вкладку Элемент управления. Щелкните по кнопке сворачивания в поле Формировать список по диапазону и выделите диапазон ячеек с фамилиями студентов без заголовка на листе Список студентов. Разверните вкладку. Щелкните по кнопке сворачивания в поле Связь с ячейкой. Щелкните по ячейке А20. В поле Количество строк введите значение 6;

· Перейдите на вкладку Свойства. Снимите флажок Выводить объект на печать. Закройте окно Форматирование объекта кнопкой ОК.

· Проверьте правильность работы поля со списком, наблюдая за номером элемента, отображаемого в ячейке А20 при выборе фамилии в списке;

· Присвойте диапазону ячеек, в котором находится список, имя Список. Для этого выделите диапазон ячеек, содержащий все данные о студентах без заголовков на листе Список студентов, введите в поле имен имя Список и нажмите клавишу Enter;

· Введите в ячейку D4 формулу для отображения выбранной фамилии:

=ИНДЕКС(Список;$A$20;1)

Примечание. Для ввода в качестве аргумента имени диапазона выберите имя Список на вкладке Формулы в группе Определенные имена из списка Использовать в формуле.

· Введите в ячейку Е4 формулу для отображения года рождения:

=ИНДЕКС(Список;$A$20;5);

· Аналогично введите в ячейку В7 формулу для отображения номера группы, а в ячейку D7 – формулу для вывода наименования специальности.

· Окончательно проверьте работу поля со списком. Выполните предварительный просмотр справки. Для этого выполните команду Файл/Печать и в появившемся окне предварительного просмотра убедитесь, что поле со списком для выбора студента не будет выводиться на печать.

32. Сохраните рабочую книгу на диске в файле с именем lab6.xlsm, причем в окне Сохранение документа в списке Тип файла выберите тип файла Книга Excel с поддержкой макросов.

Примечание. Чтобы запустить макросы после открытия сохраненной книги, необходимо установить уровень безопасности, временно разрешающий выполнение всех макросов. Для этого:

· на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросов;

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

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

Список литературы

1. Сурядный А. С. Microsoft Office 2010. Лучший самоучитель. – М.: АСТ, Астрель, ВКТ, 2011. – 512 с.

2. Васильев А. А., Стоцкий Ю. А., Телина И. С. Microsoft Office 2010. Самоучитель. – Спб.: Питер, 2011. – 432 с.

3. Курбатова Е. Microsoft Office Excel 2010. Самоучитель. – М.: Диалектика/Вильямс, 2010. – 416 с.

4. Уокенбах Д. Microsoft Excel 2010. Библия пользователя. – М.: Диалектика/Вильямс, 2011. – 912 с.

5. Карлберг К. Бизнес-анализ с использованием Excel. – М.: Вильямс, 2012. – 576 с.

6. Уокенбах Д. Формулы в Microsoft Excel 2010. – М.: Диалектика, 2011.—704 с.

7. Сингаевская Г.Функции в Microsoft Office Excel 2010. – М.: Диалектика/Вильямс, 2011. – 1094 с.

8. Джелен Б., Александер М. Сводные таблицы в Microsoft Excel 2010. – М.: Диалектика/Вильямс, 2011. – 464 с.

[1] Надстройка – вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.

[2] Макрос – действие или набор действий, используемые для автоматизации выполнения задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA).

Дата добавления: 2014-11-25