Resources

    All about Visual Basic for Application (VBA) especially for Microsoft Excel.

Subscribe

  • Subscribe

how to get row and column of cell in excel in worksheet change event?

Posted by | August 7, 2012 .

Worksheet event:

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:

  • Private Sub Worksheet_Activate()
  • Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  • Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  • Private Sub Worksheet_Calculate()
  • Private Sub Worksheet_Change(ByVal Target As Range)
  • Private Sub Worksheet_Deactivate()
  • Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  • Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  • Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For certain event if you notice we have “ByVal Target as Range”. What is the meaning? Huh

Worksheet event in Microsoft Excel

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.

worksheet event change in Microsoft excel

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.

Worksheet event in Microsoft Excel

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

Share Button
Share on Facebook
Bookmark this on Digg
Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Bookmark this on Delicious

Leave a Comment

If you would like to make a comment, please fill out the form below.

Name (required)

Email (required)

Website

Comments