Please help with code to plug date in A1 only on certain she

[pilot]

Worksheets are named like *1, *2, etc. I want to plug the current date in A1 of the active sheet if the sheet changes but only if its name is like *2.

My feeble attempt:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
For ActiveSheet
If Sh.Name = Like "*2" Then
Sh.[A1] = Date
End If
Application.EnableEvents = True
End Sub

I know there's a problem with the "For..." line but I don't want to have to loop through every sheet. How can I have this work just on the active sheet?

[Jay Petrulis]

Hi,

This worked for me with the SheetActivate event.

----------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
If Sh.Name Like "*2" Then
Sh.[A1] = Date
End If
Application.EnableEvents = True
End Sub
----------------------

Did you want the code to cycle through all worksheets and add the date in A1 if the active sheet ends in 2?

Regards,
Jay

[pilot]

I can't use the Sheet Activate event. There is lots of looking but not much changing. I only want to update A1 with current date if a change is made. So I only want the If statement to run against the active sheet.

[pilot]

It just struck me. This code will only be placed on worksheets like *2 so I don't even need the If statement. Duh!

[pilot]

Pasted this code into sheets like *2 but nothing happens when sheet is changed. What's wrong?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
Sh.[A1] = Date
Application.EnableEvents = True
End Sub

[Jay Petrulis]

Hi,

OK, now I understand. One thing -- are you going to insert the code in a sheet module on any sheet with a name ending in 2?

No need, your code works fine if you take out the = sign before the Like and delete the For ... line

This will ensure that you are always covered on this and will be more efficient.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False
If Sh.Name Like "*2" Then
Sh.[A1] = Date
End If
Application.EnableEvents = True
End Sub

[pilot]

Thanks, Jay. That seems to work. The only thing I don't fully understand is what's the difference in putting this code in a module vs. This Workbook?