VBA Code to know the date of creating a new worksheet

[YasserKhalil]

Hi everybody
I have a strange request!!
Is there a code to know the date when a worksheet is created??

[mdmackillop]

This should record the creation in Sheet 3. It assumes 3 original sheets. Place in ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Right(Sh.CodeName, Len(Sh.CodeName) - 5) > 3 Then
Sheet3.Cells(Rows.Count, 1).End(xlUp)(2) = Sh.CodeName
Sheet3.Cells(Rows.Count, 2).End(xlUp)(2) = Now
End If
End Sub

[YasserKhalil]

Thanks a lot Mr. mdmackillop
I want to record the name of the sheet not the code name...
Another thing ::what about existing worksheet , is there any way to record when they were created??

[mdmackillop]

I want to record the name of the sheet not the code name...
I'm sure you can work that out for yourself.


Another thing ::what about existing worksheet , is there any way to record when they were created??
You could save in the "log" when the workbook was created. I know of no way to retrospectively obtain sheeet creation data.

[Simon Lloyd]

You need to use the workbook event Workbook_Newsheet, put this in the ThisWorkbook code module as a demo, add/insert a new sheet and you get a message, you should then be able to adapt it :)
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox Sh.Name & " was created " & Now
End Sub