Excel. Трюки и эффекты

Листинг 2.36. Ограничение возможных значений диапазона

Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rgInputRange As Range

Dim cell As Range

Dim strMessage As String

Dim varResult As Variant

' Диапазон, в котором контролируется ввод

Set rgInputRange = Range(«A1:E10»)

' Просмотр всех измененных ячеек и контроль ввода в тех,

которые _

принадлежат заданному диапазону

For Each cell In Target

' Проверка принадлежности диапазону

If Union(cell, rgInputRange).Address =

rgInputRange.Address Then

' Контроль правильности ввода

varResult = IsCellDataValid(cell)

If varResult = True Then

' Введено корректное значение

Exit Sub

Else

' Формирование и вывод сообщения об ошибке

strMessage = "Ячейка " & cell.Address(False, False) &

":" _

& vbCrLf & vbCrLf & varResult

MsgBox strMessage, vbCritical, «Неправильное значение»

' Очистка ввода

Application.EnableEvents = False

cell.ClearContents

cell.Activate

Application.EnableEvents = True

End If

End If

Next cell

End Sub

Function IsCellDataValid(cell As Range) As Variant

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

в диапазоне от 1 до 12. В противном случае выдается _

соответствующее сообщение

' Проверка, является ли содержимое ячейки числом

If Not WorksheetFunction.IsNumber(cell.Value) Then

IsCellDataValid = «Нечисловое значение»

Exit Function

End If

' Проверка, является ли введенное число целым

If Int(cell.Value) <> cell.Value Then

IsCellDataValid = «Введите целое число»

Exit Function

End If

' Проверка соответствия числа диапазону

If cell.Value < 1 Or cell.Value > 12 Then

IsCellDataValid = «Значение должно быть от 1 до 12»

Exit Function

End If

' В ячейку введено допустимое значение

IsCellDataValid = True

End Function

После написания данного кода в диапазон А1:Е10 можно будет вводить только целые числовые значения, попадающие в интервал от 1 до 12. При попытке ввода нечислового значения (например, текста) программа не позволит этого сделать – на экране отобразится окно с сообщением Нечисловое значение. Ввод дробного числа также будет невозможен – появится сообщение Введите целое число. Если же попытаться ввести значение, выходящее за рамки интервала от 1 до 12, то это также окажется невозможным и будет выдано сообщение Значение должно быть от 1 до 12.

Последовательный ввод данных

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

Смысл операции заключается в том, что необходимые данные будут вводиться в диалоговом окне и лишь после нажатия ОК они займут свое место в таблице. Сразу после этого в диалоговом окне можно будет вводить уже следующие данные и т. д. И все это – независимо от расположения курсора. Реализацию данной возможности рассмотрим на конкретном примере.

Предположим, что в ячейки столбца А необходимо последовательно ввести перечень дат, а в ячейки столбца В – торговую выручку, соответствующую каждой дате столбца А. Решить эту задачу можно с помощью макроса, код которого (он должен быть помещен в стандартный модуль) приведен в листинге 2.37.