Some time necessary to use this event to trigger something for example pop up message to alert user, to update data and etc. Open your Visual basic Editor (With Excel open press Alt + F11) at your left panes under VBA project you will see by default Sheet1, Sheet2, Sheet3 and This Workbook (Click plus sign at Microsoft Excel Objects if hidden) and right click any sheets and view code. On top of this empty area if you click at left drop down list you will see worksheet, just click this worksheet. After you click this worksheet go and click at your right drop down list and you will found all the event available for worksheet as below, just click any event it will automatically create private sub for you and you can start writing VBA code here:
For certain event if you notice we have “ByVal Target as Range”. What is the meaning? Huh
Actually certain event react with Cells and certain with sheet itself. When this event react with Cells then we have this “ByVal Target as Range” so that we can know actually which cells is affected.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim tRow As Long Dim tCol As Integer Dim StrAfterChg As String tRow = Target.Row tCol = Target.Column StrAfterChg = Cells(tRow, tCol).Text MsgBox "Row index = " & tRow & Chr(10) & "Column index = " & tCol & _ Chr(10) & "Change to = " & StrAfterChg End Sub
Let’s try the above example. Copy the above code and paste into sheet1 (Right click and view code) and do not copy paste into ordinary module.
With the above code when we change value or text at any cells it will pop up message tell us what is row and column index and also text after changed.
Next question how to get the text string before change event? Stay with me.. Next post
If you have any question or project please e-mail to me, I will reply and create for free depending on my free time.
Click Here to E-mail