Worksheet change event

Worksheet change event
We have already touched on the "event" in the previous post. If you still don't know, go back and take a few minutes to take a look.
When the cell value of a worksheet is modified, the change event of worksheet is triggered. We can write some statements to respond to this event to complete some work. Here we will use some examples to understand it.
Open a workbook, click the worksheet name tab, and select the "view code" command to enter the VBE edit window. In the code window, select "worksheet" in the object list ", select "change" in the event list. A piece of code appears in the code window:
Code:
Private sub worksheet_change (byval target as range)

End sub
1. worksheet is the object name, change is the event name, and the Object Name and event name are connected by underline;
2. (byval target as range): The parameter passed by the program running in parentheses. Target is a range object, that is, the cell area that triggers the change event, that is, the cell where the value is modified.
3. We only need to write the code to be executed between sub and end sub. In this way, when a cell in the worksheet changes, the program code between sub and end sub is automatically executed.

If we need to tell us the address of this cell after each cell modification, the code can be:
Code:
Private sub worksheet_change (byval target as range)
Msgbox "the address of the cell you just modified is:" & target. Address
End sub
In this way, when a cell is modified, a dialog box will pop up automatically to show us the address of the Modified cell.

If you want to modify a cell area, such as a cell in column D, this dialog box will pop up to tell us that it is also possible, the code is:
Code:
Private sub worksheet_change (byval target as range)
If target. Column = 4 then 'when the column number of the changed cell is 4, that is, column D, the program is executed.
Msgbox "the address of the cell you just modified is:" & target. Address
End if
End sub
Sometimes, in order to reduce the input of worksheet information, we use the change event of worksheet, for example, a worksheet that records students' borrowing or returning books, the areas below column C record the "lending" or "return" of books. Each time you change the "lending" or "return" status, if the date is changed in column D of the same row, the code can be:
Code:
Private sub worksheet_change (byval target as range)
'When there is only one modified cell, and the column number is equal to 3, and the row number is greater than 2, the program is executed.
If target. Count = 1 and target. Column = 3 and target. Row> 2 then
Target. offset (0, 1) = date' the value of a cell on the right of the Modified cell is the current date.
End if
End sub