Событие листа

Worksheet_SelectionChange

Рассмотрим макрос, который реагирует на выделение ячейки, иногда это бывает полезным и нужным. Т.е. мы навели мышку на ячейку и при этом у нас сработал какой-то макрос. Вот и посмотрим как это делается.

Заходим в окно VB, нажимая Alt+F11. Слева мы видим названия наших листов. Щёлкаем двойным щелчком по желаемому листу. Вверху имеется два окошка в которых по умолчанию написано General и Declarations.

Выбираем в левом окне Worksheets, а в правом SelectionChange.

После этого появится следующая запись кода:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)

      End Sub

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

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)

          Selection.Interior.ColorIndex = 5

      End Sub

то какой бы мы диапазон не выделили на этом листе, он окрасится в синий цвет.

Хочу обратить внимание, что макрос работает только на том листе, который Вы выбрали слева в списке листов.

Рассмотрим ещё один пример, в котором будет выделяться всегда только одна ячейка на листе - активная ячейка (в видеоролике показано как это работает).

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)

          ActiveCell.Select

      End Sub

Необходимо только запомнить одно, - что вы можете вставить на лист только один макрос с таким именем, т.е. макрос создающий событие при выделении ячейки. Если вы хотите чтобы происходило несколько разных событий в зависимости от условий на листе, то это необходимо описывать всё в этом же макросе. Ниже приведён пример как макрос выдаёт различные информационные сообщения в зависимости от положения курсора на листе.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)

          a = ActiveCell.Column

      If a < 10 Then

          MsgBox "Курсор находится на столбце №" & a & " - это меньше 10.", _

          vbInformation, "Пример"

      Else

          MsgBox "Курсор находится за пределами моего понимания!", _

          vbInformation, "Пример"

      End If

      End Sub

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

Worksheet_Change

Рассмотрим событие, которое возникает при редактировании ячейки. Т.е. мы внесли какие-то данные в ячейку, вышли из режима редактирования и при этом у нас сразу сработал какой-то макрос.
Делается это следующим образом. Заходим в окно VB, щёлкаем слева по листу, событие которого хотим отслеживать. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet и правом окошке Change.

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

      Private Sub Worksheet_Change(ByVal Target As Range)

      End Sub

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

      Stolbec = Target.Column

      Stroka = Target.Row

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

      Private Sub Worksheet_Change(ByVal Target As Range)

      Stolbec = Target.Column

      Stroka = Target.Row

      MsgBox "Отредактирована ячейка Cells(" & Stroka & "," & Stolbec & ")", _

              vbInformation, "Пример"

      End Sub

Рассмотрим такой пример когда, после редактирования ячейки, её текст автоматически закрашивается в синий цвет, а ячейка окрашивается в жёлтый цвет.

      Private Sub Worksheet_Change(ByVal Target As Range)

          Target.Font.ColorIndex = 5 'синий

          Target.Interior.ColorIndex = 6 'жёлтый

      End Sub

И всё бы ничего, отредактировали ячейку, потом выпполнился какой-то макрос. Но вот беда если записать такой макрос, то мы загрустим:

      Private Sub Worksheet_Change(ByVal Target As Range)

          Target.Cells = "Привет"

      End Sub

А загрустим потому, что мы будем заносить в изменённую ячейку какие-то данные. То-есть мы опять её редактируем. Следовательно, как только макрос заносит данные в ячейку, он снова запускает сам себя. И у нас получается зацикливание. Для того чтобы этого не происходило мы используем дополнительную запись, которая блокирует запуск события в момент выполнения текущего.

      Private Sub Worksheet_Change(ByVal Target As Range)

          Application.EnableEvents = False

              Target.Cells = "Привет"

          Application.EnableEvents = True

      End Sub

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

Двойной щелчок мыши. BeforeDoubleClick

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

Что можно сделать при помощи такого макроса? Таблицы, в которых данные можно заносить при помощи двойного щелчка мыши. Например, устанавливать галочки напротив выбранных наименований.

Делается это следующим образом. Заходим в окно VB, щёлкаем слева по необходимому листу. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet, а правом окошке BeforeDoubleClick.

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

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

      End Sub

Если в этот код поместить какое-нибудь информационное сообщение, то при двойном щелчке по ячейке, будет появляться это сообщение.

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

          MsgBox "Вы совершили двойной клик!", vbInformation, "Пример"

      End Sub

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

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

      Cancel = True

          MsgBox "Вы совершили двойной клик!", vbInformation, "Пример"

      End Sub

Cancel = True может принимать только два значения True или False и об этом указано в наименовании макроса Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean).

Но такая запись активизирует двойной щелчёк на всём листе, и это конечно хорошо, но иногда возникает потребность задействовать только часть листа, например только столбец А. На этом этапе мы уже начинаем придумывать какие-то условия. И это можно осуществить всё при помощи того же If ... End If или Select Case ... End Select.

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

          If ActiveCell.Column = 1 Then

              Cancel = True

              MsgBox "Вы совершили двойной клик!", vbInformation, "Пример"

          End If

      End Sub

При такой записи сообщение будет появляться только в том случае, если мы щёлкаем мышкой по столбцу А.

Усложним пример. Сделаем так, как показано в начале урока на левом рисунке. Попробуем Установить галочки, причём не во всём столбце, а в некотором диапазоне, например В2:В8. Ниже приведён макрос, который может это осуществить.

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

      If Not Intersect(Target, Range("B2:B8")) Is Nothing Then 'ищем пересечение в данном диапазоне

      Target.Font.Name = "Marlett" 'Задаем текст при двойном шелчке и шрифт текста, в котором символ А и есть галочка

      Cancel = True

          If Target = "a" Then 'Искомый текст

              Target.Interior.ColorIndex = 23 'цвет синий

              Target = ""

          Else

              Target = "a"

              Target.Interior.ColorIndex = 6

          End If

      End If

End Sub

Теперь самое время пояснить его составляющие.

If Not ... Is Nothing Then - если производится действие в указанном диапазоне, то выполняем условие. Другими словами, если ячейка, по которой произвели двойной щелчок, попадает в диапазон В2:В8, то выполняем макрос дальше после слова Then;

Intersect(Target, Range("B2:B8")) - это указывает переменную, которая должна принимать участие в диапазоне В2:В8, т.е. пересекаться в этом диапазоне, в данном случае это изменяемая ячейка;

Target.Font.Name = "Marlett" - устанавливаем имя шрифта в ячейке, который и отвечает за галочку;

If Target = "a" Then ... - если в редактируемой ячейке стоит галочка, то убираем её и закрашиваем ячейку в синий цвет. В противном случае устанавливаем галочку и закрашиваем ячейку в жёлтый цвет.

Ту же самую функцию можно осуществить через другую запись:

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

      St = Target.Column

      Sk = Target.Row

      If St = 2 And Sk > 1 And Sk < 9 Then

      Target.Font.Name = "Marlett"

      Cancel = True

          If Target = "a" Then

              Target.Interior.ColorIndex = 23

              Target = ""

          Else

              Target = "a"

              Target.Interior.ColorIndex = 6

          End If

      End If

      End Sub

Тут диапазон задаётся при помощи координат столбца и строки, а также обычных логических операторов.

Вторая табличка, которая показана в начале страницы справа, выполняется по тем же принципам, только можно не указывать стиль шрифта и вместо а, писать Вкл и Выкл.

В видеоматериале показаны примеры работ приведённых макросов, а также продемонстрирована полная запись их написания.

Правая кнопка мыши. BeforeRightClick

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

Делается это следующим образом. Заходим в окно VB, щёлкаем слева по листу, событие которого хотим отслеживать. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet и правом окошке BeforeRightClick.

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

      Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

      End Sub

И если в этот макрос вставить какое-нибудь информационное сообщение, то при нажатии на правую кнопку мыши на заданном листе, нам отобразится оно.

      Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

          MsgBox "Вы нажали на ПКМ", vbInformation, "Пример"

      End Sub

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

      Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

          Cancel = True

          MsgBox "Вы нажали на ПКМ", vbInformation, "Пример"

      End Sub

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

      Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

          Cancel = True

          a = ActiveCell.Column

          Select Case a

          Case 1

          ActiveCell.Interior.ColorIndex = 3

          Case 2

          ActiveCell.Interior.ColorIndex = 4

          Case 3

          ActiveCell.Interior.ColorIndex = 5

          Case 4

          ActiveCell.Interior.ColorIndex = 6

          Case 5

          ActiveCell.Interior.ColorIndex = 7

          Case Else

          ActiveCell.Interior.ColorIndex = xlNone

          End Select

      End Sub

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

Если выполнить вот такую запись:

      Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

          Cancel = True

      End Sub

то правая кнопка мыши просто напросто не будет "работать", то-есть меню не будет появляться.

Добавление своего раздела в меню правой кнопки мыши

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

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

        Dim a As Object

        For Each a In Application.CommandBars("cell").Controls

            If a.Tag = "brccm" Then a.Delete

        Next a

            With Application.CommandBars("cell").Controls.Add(Type:=msoControlButton, _

            before:=1, temporary:=True)

               .Caption = "Окрасить ячейку"

               .OnAction = "cvet1"

               .Tag = "brccm"

            End With

            With Application.CommandBars("cell").Controls.Add(Type:=msoControlButton, _

            before:=2, temporary:=True)

               .Caption = "Обесцветить ячейку"

               .OnAction = "cvet0"

               .Tag = "brccm"

            End With

    End Sub

При выборе "Окрасить ячейку", выделенный диапазон заливается синим цветом. При нажатии на "Обесцветить ячейку", выделенный диапазон ячеек обесцвечивается.
Работу данного кода можно посмотреть на "Листе 3" в примере. На видеоролике так же можно посмотреть его работу.