VBA-Урок 11.2. События рабочего листа (Worksheet Events)
На предыдущем уроке мы рассматривали события, относящиеся к целой книги. Сейчас мы сфокусируемся на событиях связанных с отдельным листом.
Worksheet_SelectionChange (Открытие книги)
Чтобы выполнить инструкции, основанные на событиях для отдельного листа, выберите лист в редакторе, а затем Worksheet :
Событие SelectionChange будет добавлено по умолчанию. Это событие выполняется когда бы не изменялось содержание диапазона:
Например, следующий код добавляет цвета заливки одного или более выбранных ячеек и автоматически удаляет цвет заливки от предыдущего выбранного диапазона, когда данный диапазон меняется:
Worksheet_Activate (Событие активации листа)
Это событие возникает при активации рабочего листа
Worksheet_Deactivate (Событие деактивации листа)
Это событие возникает при активации другого рабочего листа
Worksheet_BeforeDoubleClick (Событие двойного щелчка по ячейке)
Это событие возникает при двойном щелчке на ячейке рабочего листа:
Worksheet_BeforeRightClick (Событие перед правым кликом)
Это событие возникает перед самым кликом правой кнопки мыши по рабочему листу
Worksheet_Calculate (Событие перерасчета листа)
Это событие возникает каждый раз, когда рассчитываются или пересчитываются данные на рабочем листе
Worksheet_Change (Событие изменения содержимого ячейки)
Это событие возникает каждый раз, когда изменяется содержимое ячеек на заданном листе:
Worksheet_FollowHyperlink (Событие нажатия на ссылку)
Это событие возникает при нажатии на ссылку (гипертекст)
Temporarily deactivate all events (Временное отключение всех событий)
Чтобы выполнять код без каких-либо событий, вставьте его между двумя строчками такого кода:
VBA Excel. Событие Worksheet.SelectionChange
Синтаксис процедуры, выполнение которой инициируется событием Worksheet.SelectionChange:
Эта процедура VBA Excel запускается при смене на рабочем листе выделенного диапазона (SelectionChange). Она должна быть размещена в модуле рабочего листа Excel, смена выбранного диапазона ячеек которого будет инициировать ее запуск.
Шаблон процедуры можно скопировать и вставить в модуль рабочего листа, но не обязательно. Если открыть модуль нужного листа, выбрать в левом верхнем поле объект Worksheet , шаблон процедуры будет добавлен автоматически:
У объекта Worksheet есть и другие события, которые можно выбрать в правом верхнем поле модуля рабочего листа. Процедура с событием SelectionChange добавляется по умолчанию.
Примеры кода с Worksheet.SelectionChange
Пример разработчика
Замечательный пример дан на сайте разработчика:
При выборе на листе любого диапазона, в том числе отдельной ячейки, лист автоматически прокручивается по горизонтали и вертикали, пока выделенный диапазон не окажется в верхнем левом углу экрана.
Эта процедура работает и при выборе ячейки через адресную строку (слева над обозначениями столбцов), и при выборе из кода VBA Excel, например:
Выбор одной отдельной ячейки
Инициируем выполнение основных операторов процедуры с событием Worksheet.SelectionChange выбором одной отдельной ячейки:
Основной оператор MsgBox «Выбрана ячейка E5» будет выполнен при выборе ячейки E5.
Примечание:
В условии примера используется свойство Address переменной Target , так как в прямом выражении Target = Range(«E5») по умолчанию сравниваются значения диапазонов. В результате этого, при выборе другой ячейки со значением, совпадающим со значением ячейки E5, равенство будет истинным и основные операторы будут выполнены, а при выборе более одной ячейки, будет сгенерирована ошибка.
Выбор диапазона с заданной ячейкой
Выполнение основных операторов процедуры при вхождении заданной ячейки в выбранный диапазон:
Основной оператор MsgBox «Ячейка B3 входит в выбранный диапазон» будет выполнен при выделении диапазона, в который входит ячейка B3, в том числе и при выделении одной этой ячейки.
Выбор ячейки в заданной строке
Инициируем выполнение основных операторов процедуры с событием Worksheet.SelectionChange выбором любой отдельной ячейки во второй строке:
Дополнительный оператор If Target.Count > 1 Then Exit Sub необходим для выхода из процедуры при выделении более одной ячейки. Причина: при выделении произвольного диапазона, ограниченного сверху второй строкой, выражение Target.Row = 2 будет возвращать значение True , и операторы в блоке If . End If будут выполнены.
Ввод даты в ячейку первого столбца
Автоматическое добавление текущей даты в выбранную ячейку первого столбца при условии, что предыдущая ячейка сверху не пустая, а ячейка снизу – пустая:
Этот код VBA может быть полезен при ведении реестра, базы данных на листе Excel с записью текущей даты в первой колонке.
Условие If Target.Count > 1 Or Target.Row = 1 Or Target.Row = ActiveSheet.Rows.Count Then Exit Sub завершает процедуру при выборе более одной ячейки, при выборе ячейки A1 и при выборе последней ячейки первого столбца.
Выбор ячейки A1 приводит к ошибке при проверке условия Target.Offset(-1, 0) <> «» , так как происходит выход за границы диапазона рабочего листа.
Ошибка выхода за пределы рабочего листа происходит и при проверке условия Target.Offset(1, 0) = «» , если выбрать последнюю ячейку первой колонки.
Примечание:
Текущая дата будет введена в следующую пустую ячейку первого столбца при переходе к ней от заполненной в том числе нажатием клавиши «Enter».
Пример без отслеживания Target
Если необходимо, чтобы процедура запускалась при любой смене выделенного диапазона, аргумент Target можно не отслеживать:
Worksheet Change and SelectionChange Events
In this tutorial, we’ll discuss the Change and ChangeSelection worksheet events. The Worksheet_Change event-handler procedure executes whenever any cell in the worksheet is changed and Worksheet_SelectionChange event-handler procedure executes when the selection on the worksheet is changed.
The worksheet event-handler procedures must be in the code module for that worksheet. Put them somewhere else, and they won’t work. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code :
Worksheet_Change event procedure
The Change event triggers whenever any cell in the worksheet is changed. Excel uses the Worksheet_Change event-handler procedure to trap the Change event. The Worksheet_Change procedure accepts Target (the Range object) as the parameter which represents the cell that was changed. The following example displays a message box that shows the address of the Target range:
Try making some changing in cells, every time you make changes, a message box displays the address of the cell that changed.
Monitor changes made to specific cell or range
The Worksheet_Chnage procedure receives the Target as Range object which represents the changed cell(s). In this example, we compare the Target with the given cell range A1:A10 using Intersect method:
A popup message box appears when a change made in the given cell range:
Worksheet_Change Vs. Worksheet_Calculate
The Worksheet_Change event procedure is not executed by a calculation change, for example, when a formula returning a different value. You must use the Worksheet_Calculate event procedure to capture the changes to values in cells that contain formulas.
Worksheet_SelectionChange event procedure
The Worksheet_SelectionChange event procedure executes when a cell is selected. The following code highlights the active cell with a red color every time a different cell is selected:
The first statement removes the background color for all cells in the worksheet. Next, the the active cell is shaded with red color.
Take some action when specific cells or ranges selected
In many cases, you need to execute a piece of code when certain cells or ranges selected. To accomplish this, we use the Intersect method on the Target (selected cell or range) and the range containing the specific cell to verify the Target is one of the specific cells or ranges. If the Target is in the range containing the specific cells, you can execute the code.
The following code highlights the active cell with a red color every time a different cell is selected:
report this ad
The Worksheet Events in Excel VBA
You may want to run your macro/VBA snippet when a cell changes its value, when a double click happens, when a sheet is selected, etc. In all these cases we use Worksheet Event Handler. The Event Handler helps us run VBA code whenever a certain event occurs.
In this article, we will learn briefly about each Worksheet Event Handler.
What is a Worksheets Event Handler?
A worksheet event handler is a subroutine that is local to a worksheet module.
Where to write Worksheet Event Handler Code?
The worksheet Events are written in sheets objects only. If you write a worksheet event in some module or class module, there will be no error but they will just won’t work.
To write in the sheet object. Double click on it or right-click and click on view code. The code writing area will be shown.
How to write code for a specific event on the worksheet?
Now when you are in the editing mode, in the top-left corner dropdown menu you will see general. Click on the drop-down and select worksheet. Now in the top-right corner dropdown, all events will show. Choose whichever you need and a skeletal code for that event will be written for you.
Each event has a fixed procedure name. These are the reserved subroutine names. You can’t use them for other subroutines on a sheet. In a module, they will work as a normal subroutine.
Important: Each subroutine from that list will run on the specified event.
One type of worksheet event procedure can be written only once on one sheet. If you write two same event handling procedures on one sheet, it will result in an error and none of them will be executed. Of course, the error will be ambiguous subroutines.
Let’s learn briefly about each of the events.
1. The Worksheet_Change (ByVal Target As Range) Event
This event triggers when we make any change to containing worksheets (formatting excluded). If you want to do something if any change made in the entire sheet then the code will be:
The «Target» is the Active cell always.
Another example: You may want to put date and time in Cell B1 if A1 changes. In that case, we use the worksheet_change event. The code would look like this:
This will target only the cell A1.
If you want to target a range then use the below example:
2. The Worksheet_SelectionChange(ByVal Target As Range) Event
As the name suggests, this event triggers when the selection changes. In other words, if your cursor is in Cell A1 and it moves to some other cell, the code in this subroutine will run.
The below code will change the active cells color if whenever it changes and if it is an even row.
Now, whenever my cursor will move on even row, it will be colored. Odd row cells will be spared.
Another Example of the Worksheet_SelectionChange event:
3. The Worksheet_Activate() Event
This event is triggered when the event code containing sheet activates. The skeletal code for this event is:
A simple example is showing the sheet name when it gets selected.
As soon as you will come on the sheet that contains this code, the event will run and will be shown a message that «You are on sheet name» (sheet2 is in my case).
4. The Worksheet_Deactivate() Event
This event triggers when leaving the code containing sheet. In other words, if you want to do something, like hiding rows or anything when you leave the sheet, use this VBA event. The syntax is:
The below example Worksheet_Deativate event will simply pop up a message that you have left the master sheet, when you will leave this sheet.
5. The Worksheet_BeforeDelete() Event
This event triggers when you confirm the deletion of the VBA event containing sheet. The syntax is simple:
The below code will ask you if you want to copy the content of the about-to-delete sheet.
6. The Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Event
This event triggers when you double click on the targeted cell. The syntax of this VBA Worksheet Event is:
If you don’t set the target cell or range, it will fire on every double click on the sheet.
The Cancel variable is a boolean variable. If you set it True, the default action won’t happen. It means if you double click on the cell it won’t get into editing mode.
The below code will make the cell fill with a color if you double click on any cell.
The below code targets the cell A1. If it is already filled with the specified color then it will vanish the color. It is much like a like button or check box.
7. The Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Event
This event triggers when you Right-Click on the targeted cell. The syntax of this VBA Worksheet Event is:
The below code will fill the cell with value 1 if you right-click on it. It won’t show the default right-click options since we have set the «Cancel» Operator to True.
8. The Worksheet_Calculate() Event
If you want something to happen when a excel calculates a sheet, use this event. It will trigger whenever excel calculates a sheet. The syntax is simple:
6. The Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Event
This procedure will run when you click on a hyperlink on the sheet. The basic syntax of this event handler is:
You can set the target hyperlink if you want. If you don’t set the target hyperlink, it will get executed if you click on any hyperlink on the code containing sheet.
So yeah guys, these were some basic worksheet events that will be handy if you know about them. Below are some related articles that you may like to read.
If you have any doubts regarding this article or any other excel/VBA related article, let us know in the comments section below.
Related Articles:
Using Worksheet Change Event To Run Macro When any Change is Made | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.
Run Macro If Any Change Made on Sheet in Specified Range | To run your macro code when the value in a specified range changes, use this VBA code. It detects any change made in the specified range and will fire the event.
Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don’t need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.