Change the name of worksheet by cell reference?
 

I am trying to set the name of a worksheet tab by referencing a cell. For
instance:

Sheet 1 is named "Week Begin: ".
In cell a1 I put the date: 9/17/07.
Sheet 1 is now named "Week Begin: 9/17/07".

Is this possible?

God Bless

Frank Pytel

Change the name of worksheet by cell reference?
 

Hi Frank,
You realize you need a macro:
http://www.mvps.org/dmcritchie/excel....htm#havemacro

Would suggest you put the date in yyyy-mm-dd format
so you can sort worksheets.
http://www.mvps.org/dmcritchie/excel...#sortallsheets

sub testxyz()
On Error Resume Next
ActiveSheet.Name = "Week Begin " & Format(Range("A1"), "yyyy-mm-dd")
If Err.Number < 0 Then
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo 0
End Sub

You cannot use a colon in the worksheetname, Excel 2007 does not warn tell
you when you try to type in invalid characters into the sheetname : \ / ? * [ or ]
but it will not let you key it in. With the macro you would
get an error with the appropriate message with err.description
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Frank Pytel" wrote in message ...
I am trying to set the name of a worksheet tab by referencing a cell. For
instance:

Sheet 1 is named "Week Begin: ".
In cell a1 I put the date: 9/17/07.
Sheet 1 is now named "Week Begin: 9/17/07".

Is this possible?

God Bless

Frank Pytel

Change the name of worksheet by cell reference?
 

David;

Thanks. I really appreciate the info. Problem is that VBA is a last resort
for me. I don't like VBA. Errors and Warnings, not code!. Just to
untrustworthy in today's world. I will try to hold out for non VBA solution
first. Thanks.

God Bless

Frank Pytel

"David McRitchie" wrote:

Hi Frank,
You realize you need a macro:
http://www.mvps.org/dmcritchie/excel....htm#havemacro

Would suggest you put the date in yyyy-mm-dd format
so you can sort worksheets.
http://www.mvps.org/dmcritchie/excel...#sortallsheets

sub testxyz()
On Error Resume Next
ActiveSheet.Name = "Week Begin " & Format(Range("A1"), "yyyy-mm-dd")
If Err.Number < 0 Then
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo 0
End Sub

You cannot use a colon in the worksheetname, Excel 2007 does not warn tell
you when you try to type in invalid characters into the sheetname : \ / ? * [ or ]
but it will not let you key it in. With the macro you would
get an error with the appropriate message with err.description
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Frank Pytel" wrote in message ...
I am trying to set the name of a worksheet tab by referencing a cell. For
instance:

Sheet 1 is named "Week Begin: ".
In cell a1 I put the date: 9/17/07.
Sheet 1 is now named "Week Begin: 9/17/07".

Is this possible?

God Bless

Frank Pytel

Change the name of worksheet by cell reference?
 

Frank

While waiting for the non VBA solution, do not hold your breath.


Gord Dibben MS Excel MVP

On Fri, 21 Sep 2007 11:44:02 -0700, Frank Pytel
wrote:

Thanks. I really appreciate the info. Problem is that VBA is a last resort
for me. I don't like VBA. Errors and Warnings, not code!. Just to
untrustworthy in today's world. I will try to hold out for non VBA solution
first. Thanks.