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

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" <(E-Mail Removed)> wrote in message news:44D740E8-1C8C-4D12-8A90-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message news:44D740E8-1C8C-4D12-8A90-(E-Mail Removed)...
> >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
>

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
<(E-Mail Removed)> 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.