Renaming a sheet based on a cell value in that sheet!

Posted by Edgar on January 03, 2001 12:59 PM

I'm trying to create a Marcro that renames a sheet based on a cell value
within that same sheet ("G39"). VBA wording is what's holding me back.

Any help given is truelly and greatly appreciated.

Thanks in advance!

Posted by Rickey Bowers Jr. on January 03, 2001 1:50 PM

From what I've read you can only change the name of a worksheet by 'SaveAs'. So that means you have to delete the old version and save:

Dim TempWorkbookName as String

TempWorkbookName = ThisWorkbook.FullName
Thisworkbook.SaveAs [G39].Value
Kill TempWorkbookName

Have a nice day, Rickey

Posted by Tim Francis-Wright on January 03, 2001 1:54 PM

To change the name of the sheet (not the workbook),
use:-

' assumes you have already activated the sheet
ActiveSheet.Name = [G39].Value

HTH

Posted by Dave on January 04, 2001 12:37 AM

Hi Edgar

You will need to use the sheets Code Name to do this. This can be seen in the Project Explorer Window. E.g.

Sub ProtectedOrNot()
On Error GoTo Invalidname
Sheet1.Name = Trim([a1])
Exit Sub
Invalidname:
MsgBox [a1] & "Is not a valid sheet name", vbCritical
End Sub

You dont need the trim but I would leave the on Error.

Dave

OzGrid Business Applications