Private Sub Worksheet_Change(ByVal Target As Range) Not working

[HarryP96]

Hi,

I am trying to trigger Macros according to the value cell A2 of Sheet1 is changed to. After changing the value in A2 nothing seems to be happening. What am I missing? Cell A2 is merged with everything up to cell B7, but I have also tried without the cell being merged.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Sheets("Sheet1").Range("A2")) Is Nothing Then
    If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "1" Then Call Athlete1
    If Target.Value = "2" Then Call Athlete2
    End If
End Sub

Sub Athlete1()
    At1 = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
    ThisWorkbook.Sheets("Sheet2").Cells(At1, 1) = Time
End Sub

Sub Athlete2()
    At2 = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
    ThisWorkbook.Sheets("Sheet2").Cells(At2, 2) = Time
End Sub

End Sub

 

[rollis13]

The only reason the event doesn't fire is because the macro Worksheet_Change isn't in the Sheet1's module.
It's useless to indicate in the macro Sheets("Sheet1").Range("A2"), Range("A2") will do as long as the macro is in the right sheet module.

 

[HarryP96]

The only reason the event doesn't fire is because the macro Worksheet_Change isn't in the Sheet1's module.
It's useless to indicate in the macro Sheets("Sheet1").Range("A2"), Range("A2") will do as long as the macro is in the right sheet module.

Thanks, I have removed Sheets("Sheet1") but unfortunately, it is still not firing. I have run athlete1 and athlete2 on their own and they are running as expected.

 

[Alex Blakenburg]

In A2 are you entering the numbers 1 and 2 as text ?
If not then lose the quotation marks in your If statements.
eg If Target.Value = 1

 

[HarryP96]

In A2 are you entering the numbers 1 and 2 as text ?
If not then lose the quotation marks in your If statements.
eg If Target.Value = 1

Thanks for your response. I have removed the quotation marks as suggested. I have also ensure A2 is formatted as a number, but still no luck. Formatting as "General" also did not seem to work.

 

[Alex Blakenburg]

The sub is definitely in the "Worksheet module" of Sheet 1 right ?
Do you know how to set a breakpoint ?
Set a breakpoint on the first if statement in the code. (Click in the left margin next to the if statement, you should see a big dot, brown on my machine).
Enter 1 or 2 in A2. Switch to vba and see if the first line is yellow. If yes the macro is triggering. <F8> through the code to see which if statement path it takes.

 

[rollis13]

If your macro is in the right place try launching from the 'Immediate' panel the function:
Application.EnableEvents = True
Maybe it has been temporarily turned off (False).

 

[HarryP96]

Thanks everyone for your help. I was under the false impression that by right clicking on sheet1 and selecting insert module had done enough to put this "within" sheet 1. In reality, I needed to right click sheet1 within the project explorer and select "View Code". After pasting the code in there, it worked. Thanks again for your help!

 

[rollis13]

Thanks for the positive feedback, glad we were of some help.