Name Sheet Tab to Reflect Edit Date
 

'Hi,

'I got this simple code, I was wondering if anybody here can help me put
the 'value of "_timestamp" (see below) as name of my worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now

End Sub

Name Sheet Tab to Reflect Edit Date
 

Hi Gerard,

Set the format "dd mmm yyyy hh_mm_ss" in the code to whatever format you
want. However, you cannot use some characters like a colon (:) in the sheet
name so can't use them in the date/time format.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from the value and save to date variable
dateTemp = Val(Mid(ActiveWorkbook.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub

--
Regards,

OssieMac


"Gerard Sanchez" wrote:

'Hi,

'I got this simple code, I was wondering if anybody here can help me put
the 'value of "_timestamp" (see below) as name of my worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now

End Sub

Name Sheet Tab to Reflect Edit Date
 

Hi again Gerard,

I had an error in the previous post. It works OK but because you had already
set the scope of the name to ActiveSheet in the previous line of code, I
should not have used ActiveWorkbook in the line that assigns the date to
dateTemp. Use the following corrected code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from value and save as date variable
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub


--
Regards,

OssieMac


"OssieMac" wrote:

Hi Gerard,

Set the format "dd mmm yyyy hh_mm_ss" in the code to whatever format you
want. However, you cannot use some characters like a colon (:) in the sheet
name so can't use them in the date/time format.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from the value and save to date variable
dateTemp = Val(Mid(ActiveWorkbook.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub

--
Regards,

OssieMac


"Gerard Sanchez" wrote:

'Hi,

'I got this simple code, I was wondering if anybody here can help me put
the 'value of "_timestamp" (see below) as name of my worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now

End Sub

Name Sheet Tab to Reflect Edit Date
 

It worked!
Thank you OssieMac for helping and taking the time :-)

Cheers!

--gerard


"OssieMac" wrote in message
...
Hi again Gerard,

I had an error in the previous post. It works OK but because you had
already
set the scope of the name to ActiveSheet in the previous line of code, I
should not have used ActiveWorkbook in the line that assigns the date to
dateTemp. Use the following corrected code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from value and save as date variable
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub


--
Regards,

OssieMac


"OssieMac" wrote:

Hi Gerard,

Set the format "dd mmm yyyy hh_mm_ss" in the code to whatever format you
want. However, you cannot use some characters like a colon (:) in the
sheet
name so can't use them in the date/time format.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from the value and save to date variable
dateTemp = Val(Mid(ActiveWorkbook.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub

--
Regards,

OssieMac


"Gerard Sanchez" wrote:

'Hi,

'I got this simple code, I was wondering if anybody here can help me
put
the 'value of "_timestamp" (see below) as name of my worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now

End Sub