[RESOLVED] Renaming Worksheets
I'm building a schedule in Excel with each worksheet tab indicating a particular day. My code keeps a date in one cell on each worksheet. I've been trying to write code that will rename the worksheet to exactly what is in this date cell, and nothing I've tried works. I keep getting error messages. Not even code from the macro recorder works. Does anyone know some code that will rename a worksheet to whatever is in a particular cell?
Thanks,
Danno
Re: [RESOLVED] Renaming Worksheets
To Webtest and billhuard,
I am still stuck with Excel 2000. billhuard is absolutely correct. What I didn't know is that even if you store the date in a cell correctly (no illegal characters), Excel still stores the date with a / as the divider which is an illegal character for renaming worksheets. I'm glad to hear that this is not a problem in Excel 2003, maybe my company will spring for it now.
I'm pretty new to VBA and it is just the coolest thing to see what you can do to Excel with some VBA code. The more I learn the more intoxicated with power I become!! :bigyello:
Thanks again one and all.
Danno
Re: [RESOLVED] Renaming Worksheets
Danno ...
I don't think it is a problem with Excel 2000. I tried a few things in Excel '97 AND Excel 2003 ... here are the results ...
Code:
'Cells(1,1) contains value 3/4/2006 display formatted as 3-Mar-06
'BOTH OF THE FOLLOWING RUN FINE IN XL-'97 AND XL-2003
ActiveSheet.Name = Format$(ActiveSheet.Cells(1, 1).Value, "dd-MMM-yyyy")
ActiveSheet.Name = Cells(1, 1).Text '<Here is one place where Value and Text are DIFFERENT!!!
'FAILS due to illegal characters in Sheet Name
ActiveSheet.Name = Cells(1, 1).Value
Yes, VBA is very powerful. The book "Professional Excel Development" by Bullen, Bovey, & Green (Addison Wesley) is dedicated to building amazing Graphic Form based applications on the same order as Visual Basic applications. This book proclaims that Excel is "not just spreadsheets", but a rich development platform. It really digs into the buried power of VBA running under Excel.
Good Luck and Good Programming!
Re: [RESOLVED] Renaming Worksheets
.Text and .Value can hold different values so make sure they are the same.
Re: [RESOLVED] Renaming Worksheets
Webtest,
Thanks for the code and for the book recommendation. I'll get it ordered today.
Danno :thumb:
Re: [RESOLVED] Renaming Worksheets
RobDog888,
You're right about .value and .text. I'll be careful :thumb:
Danno
Re: [RESOLVED] Renaming Worksheets
If you are interested in the "nuts and bolts" technical details of Excel VBA, another reference that I like is:
"Definitive Guide to Excel VBA"
Michael Kofler
a! Press
Neither of the books that I've mentioned will answer all of your questions, but between them and the Forum, you have access to an amazing depth of knowledge!!!
Re: [RESOLVED] Renaming Worksheets
Got it Webtest, I'll get that one too.
Danno