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

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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.
 
Upvote 0
It just struck me. This code will only be placed on worksheets like *2 so I don't even need the If statement. Duh!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top