Файл: vba_2002.pdf

События объекта Worksheet

События объекта Worksheet являются самыми полезными и часто используемыми. Контроль над ними может заставить приложение выполнять функции, которые в другой ситуации считались бы невозможными.

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

вExcel 5/95.

!Таблица 19.2. События объектаWorksheet

Этособытиепроисходит тольковExcel2002 инеподдерживаетсявпредыдущихверсиях.

Помните, что код процедуры обработки события рабочего листа должен храниться в модуле кода соответствующего объекта рабочего листа.

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

Событие Change

Событие Change возникает в момент изменения значения ячейки со стороны пользователя или со стороны внешней ссылки. Событие Change не возникает, когда расчеты приводят к появлению другого значения формулы или когда на рабочий лист добавляется новый объект.

При вызове процедуры Worksheet _ Change в качестве аргумента T a r g e t ей передается объект Range. Этот объект представляет изменившуюся ячейку или диапазон, которые привели к возникновению события. Следующий пример отображает окно сообщения, которое выводит адрес диапазона, указанного в параметре T a r g e t .

P r i v a t e Sub Worksheet_Change(ByVal T a r g e t As Excel . Range) MsgBox "Диапазон " & Target - Address & " изменился . "

End Sub

Для того чтобы получить представление о типах действий, которые приводят к возникновению события Change в рабочем листе, добавьте предыдущую процедуру в модуль кода объекта

W orksheet. После ввода этой процедуры активизируйте Excel и внесите изменения в рабочий лист, используя для этого различные методы. Каждый раз при возникновении события Change будет отображаться адрес диапазона, который изменился.

После запуска этой процедуры вами может быть замечена интересная особенность: некоторые действия, которые должны способствовать возникновению этого события, ни к чему не приводят, а те, которые не должны выполнять эту задачу, приводят к возникновению события Change!

•Изменение (]юрматнрования ячейки не приводит к возникновению события Change, а использование команды Правка^ОчиститьоФорматы, наоборот, вызывает это событие.

•Добавление, редактирование или удаление комментария в ячейке не приводит к возникновению события Change.

•Нажатие клавиши <Del> приводит к генерации события Change даже в том случае, если ячейка не содержит данных.

•Ячейки, которые изменяются с помощью команд Excel, могут генерировать, а могут и не генерировать событие change . Например, команды Данные^Форма и Данные1 * Сортировка не содействуют возникновению события Change. Зато команды Сервис* Орфография и Правка^Заменить приводят к генерированию этого события.

•Если процедура VBA изменяет содержимое ячейки, то это приводит к возникновению события Change.

Рассматривая приведенный выше список, вы убедитесь, что использовать событие Change для отслеживания изменений в ячейках нецелесообразно.

Кроме перечисленных выше особенностей, возникновение события change в ответ на определенные действия зависит и от версии Excel. 60 всех версиях Excel до 2002 заполнение диапазона с помощью команды Правка^Заполнить не приводило к возникновению события change. Подобным образом вела себя команда Правка^Удалить, которая удаляла содержимое выделенного диапазона.

Отслеживание изменений в определенном диапазоне

Событие Change возникает при внесении изменении в любую из ячеек рабочего листа. Но в большинстве случаев важно отслеживать изменения, которые вносятся в определенную ячейку или диапазон. Когда вызывается процедура обработки события Worksheet_Change, она получает в качестве параметра объект Range. Этот объект представляет диапазон ячеек или ячейку, которая после изменения приводит к возникновению события Change. Предположим, что на рабочем листе определен диапазон InputRange, и вам необходимо отслеживать только тс изменения, которые внесены в этом диапазоне. Не существует события Change для отдельного объекта Range, но можно выполнить необходимую проверку в начале процедуры Worksheet_Change:

Private Sub Worksheet__Change (ByVal Target As Excel.Range) Dim VRange As Range

Set VRange = Range("InputRange")

If Not IntersectfTarget, VRange) Is Nothing Then _ MsgBox "Изменена ячейка тбкущего диапазона."

End Sub

В данном примере используется объект Range, который называется VRange. Он представляет диапазон ячеек на рабочем листе, который необходимо проверять на предмет внесения изменений. Процедура использует функцию VBA I n t e r s e c t , чтобы определить располо-

Функция Intersect возвращает объект, который состоит из всех ячеек, содержащихся в обоях аргументах. Если функция I n t e r s e c t возвращает значение Nothing, то у этих диапазонов нет общих ячеек. Оператор отрицания Not используется для того, чтобы выражение стало равным True в том случае, если указанный диапазон имеет хотя бы одну общую ячейку с диапазоном VRange. Таким образом будет отображено окно сообщения. В противном случае ничего не происходит, и процедура завершает свою работу.

Внесениезаписей об изменениях в комментарии ячеек

В следующем примере представлено, как добавлять заметки к комментарию в ячейке при внесении в нее изменений (что определяется событием Change). Значение элемента управления CheckBox, добавленного на лист, определяет необходимость внесения заметок в комментарий ячейки. На рис. 19.5 показан пример комментария ячейки, в которую несколько раз вносились изменения.

Рис. 19.5. Процедура Worksheet_Change добавляет заметкиобизмененияхвкомментарийячейки

ЭтотпримерсодержитсянаWeb-узлеиздательства.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cell As Range

Dim OldText As String, NewText As String

If CheckBoxl Then

For Each cell In Target With cell

On Error Resume Next OldText = .Comment.Text

If Err <> 0 Then .AddComment

NewText = OldText & "Изменено на " & cell.Text & _

Selection.AutoSize = True

.Comment.Visible = False End with

Next cell End If

End Sub

Так как объект, который передан в процедуру Worksheet_Change, может состоять из многоячеечного диапазона, процедура циклически просматривает все ячейки диапазона Target . Если ячейка еще не содержит комментария, то он добавляется. Новый текст будет добавлен в конец уже существующего комментария {если такой существует).

Данный пример является обучающим. Если вам действительно необходимо отслеживать изменения в рабочем листе, воспользуйтесь командой Excel Сервис^Исправления. Эта команда может предоставить намного больше возможностей, чем приведенный выше пример.

Проверка правильности введенных данных

Средство Excel проверки данных может оказаться очень ценным инструментом, но оно связано с серьезной потенциальной проблемой. При вставке данных в ту ячейку, в которой реализуется проверка данных, значение не только не проверяется, но и правила проверки, которые связаны с этой ячейкой, безвозвратно удаляются! Таким образом, инструмент проверки данных становится практически бесполезным в собственных приложениях. В этом разделе продемонстрированы методы использования события Change объекта Worksheet для создания процедур проверки правильности введенных данных.

На Web-узле издательства содержится две версии этого примера. В одной из них используется свойство EnableEvents для предотвращения бесконечного цикла возникновения событий Change. Во второй версии применена статическая булева переменная (дополнительная информация об отключении событий приводится в разделе "Отключение событий" ранее в этой главе).

Листинг 19.1 содержит процедуру, которая выполняется каждый раз при внесении изменений в ячейку со стороны пользователя. Проверка ограничена диапазоном, называющимся I n p u t R a n g e . В этот диапазон разрешается вводить только целые значения от 1 до 12.

Листинг19.1.Определениеправильностивведенныхданных

Private Sub Worksheet_Change{ByVal Target As Excel.Range) Dim VRange As Range, cell As Range

Dim Msg As String

Dim ValidateCode As Variant

Set VRange = Range("InputRange") For Each cell In Target

If Union{cell, VRange).Address = VRange.Address Then ValidateCode = EntrylsValid(cell)

If ValidateCode = True Then Exit Sub

Else

Msg = "Ячейка " & cell.Address(False, False) & " : " Msg = Msg & vbCrLf & vbCrLf & ValidateCode

End If

End If

Next cell

End Sub

Процедура Worksheet _ Change создает объект Range. Он называется VRange и представляет проверяемый диапазон на рабочем листе. В результате циклически просматриваются вес ячейки аргумента T a r g e t , представляющего диапазон изменившихся ячеек. В коде определяется, наход*ггся ли изменившаяся ячейка в проверяемом диапазоне, и если это так, передает ячейку в качестве аргумента другой функции ( E n t r y l s V a l i d ) , возвращающей значение T r u e для действительного значения ячейки.

Если значение ячейки выходит за пределы набора допустимых значений, то функция E n t r y l s V a l i d возвращает строку, которая описывает проблему. Затем выводится окно сообщения (рис. 19.6). После закрытия окна сообщения неправильное значение ячейки удаляется, и ячейка активизируется. Обратите внимание, что перед удалением значения ячейки события отключаются. Если события не отключить, то ячейка создаст событие Change и введет процедуру в бесконечный цикл.

Рис. 19.6- Это окно сообщения описывает проблему,возникающуюпривведениемнользователе.»недопустимыхданных

Ниже приведен листинг процедуры EntrylsValid.

Листинг 19.2. Проверка принадлежности значения диапазону

Private Function EntrylsValid(cell) As Variant

'Возвращает True, если в ячейку вводится целое число в диапазоне от 1

1до 12. В противном случае возвращается строка, описывающая проблему

'Число

If Not WorksheetFunction.IsNumber(cell) Then EntrylsValid = "Нечисловое значение."

Exit Function

End If

1Целое?

If Clnt(cell) <> cell Then

EntrylsValid = "Введите целое число." Exit Function

End If

'диапазон?

If cell < 1 Or cell > 12 Then

EntrylsValid = "Значение должно быть в диапазоне от 1 до 12." Exit Function

End If

'Тест завершен успешно

EntrylsValid = True

End Function