CODEWITHSUNDEEP
excelvbaeventsautomationworksheet
Assaf
Assaf

Reputation: 89

Excel 2 Worksheets Events with Different Targets

I have an excel worksheet that I want to assign to it more than one Worksheet Event. To be more specific, I want whenever a cell in column B is changed then one cell to the left (column A) gets the row number. Also I want whenever a cell in column J is changed then one cell to the right (column K) gets today's date.

It worked for me for both of them individually but I think I may be doing something wrong using them together.

Any help will be much appreciated!

Private Sub AG1(ByVal a_Target As Range)
   If Not Intersect(a_Target, Me.Range("B2:B3000")) Is Nothing Then
      Application.EnableEvents = False
      Cells(a_Target.Row, a_Target.Column - 1) = a_Target.Row
      Application.EnableEvents = True
   End If 
End Sub




Private Sub AG2(ByVal b_Target As Range)
   If Not Intersect(b_Target, Me.Range("J2:J3000")) Is Nothing Then
       Application.EnableEvents = False
       Cells(b_Target.Row, b_Target.Column + 1) = Date
       Application.EnableEvents = True
   End If
End Sub

edit - works now (I also added that column can be referred as letter):

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = True

   If Split(Cells(1, Target.Column).Address(True, False), "$")(0) = "B" Then
      Application.EnableEvents = False
      Cells(Target.Row, Target.Column - 1) = Target.Row
      Application.EnableEvents = True

   ElseIf Split(Cells(1, Target.Column).Address(True, False), "$")(0) = "J" Then
       Application.EnableEvents = False
       Cells(Target.Row, Target.Column + 1) = Date
       Application.EnableEvents = True
   End If


End Sub

Upvotes: 1

Views: 80

Answers (1)

RCL
RCL

Reputation: 276

Copy the code in the Worksheet_Change event and that should fix your issue. This will trigger every time you enter a value for any cell and will only meet the condition if they intersect the range in the if statement.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Me.Range("B2:B3000")) Is Nothing Then
      Application.EnableEvents = False
      Cells(Target.Row, Target.Column - 1) = Target.Row
      Application.EnableEvents = True
   End If

   If Not Intersect(Target, Me.Range("J2:J3000")) Is Nothing Then
       Application.EnableEvents = False
       Cells(Target.Row, Target.Column + 1) = Date
       Application.EnableEvents = True
   End If


End Sub

Upvotes: 2

Related Questions