MrExcel Publishing
Your One Stop for Excel Tips & Solutions

change tab to cell name


Posted by Robert on September 02, 2000 8:10 PM

Is it possible to save an Excel workbook with the name of a cell using a Excel macro. I also want to change the tab name to the contents of the cell.

example: cell A1 has the contains 'Data for 01/01/2000' stored in it. I want to save the workbook as 'Data for 01/01/2000' and change the tab to 'Data for 01/01/2000'


Posted by Ivan Moala on September 03, 0100 1:31 AM

Would something like this work for you ??


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FileNm As String

FileNm = Range("A1").Text

On Error Resume Next
ActiveSheet.Name = FileNm
If Err.Number <> 0 Then Cancel = True: GoTo Errh
On Error GoTo 0
Cancel = True: Application.EnableEvents = False
ThisWorkbook.SaveAs FileNm
Application.EnableEvents = True
Exit Sub
Errh:
MsgBox "Sheet name not valid!" & Chr(13) & "Save canceled"
End Sub

NB: not fully tested and a bit messy...didn't
really look @ coding.....

Ivan