Change the name of worksheet by cell reference?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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/getstarted.htm#havemacro

Would suggest you put the date in yyyy-mm-dd format
so you can sort worksheets.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#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
 
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 said:
Hi Frank,
You realize you need a macro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

Would suggest you put the date in yyyy-mm-dd format
so you can sort worksheets.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#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 said:
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
Click to expand...
Click to expand...
 
Frank

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


Gord Dibben MS Excel MVP
 
Back
Top