excel sheet tabs
 

Cna you make the tab in an excel workbook change to the file name you save it
as?

Can this be done automatically when saving instead of manually doing it?

When you save a workbook, you can save it as the existing name or a new name.

If you save it as a new name, then you could ask for the new name and process
the rename yourself.

Another option would be to let the user save it, then get the name later. But
this would leave the workbook unsaved. (So hit the save button right away (or
add it to the code???).

Personally, I don't think it's a good idea to have the workbook name and the
worksheet name the same. It could confuse excel in some situations (and it
would confuse me in lots more situations!).

Anyway behind the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.OnTime Now() + TimeSerial(0, 0, 3), "module1.RenameSheet"
End Sub


In a general module:

Option Explicit
Sub RenameSheet()
Dim MyName As String
With ThisWorkbook
MyName = Left(.Name, Len(.Name) - 4)
On Error Resume Next
If .Worksheets(1).Name = MyName Then
'do nothing
Else
'remove the .xls
.Worksheets(1).Name = MyName
If Err.Number < 0 Then
MsgBox "Sheet renaming failed"
Err.Clear
End If
End If
On Error GoTo 0
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

CQ wrote:

Cna you make the tab in an excel workbook change to the file name you save it
as?

Can this be done automatically when saving instead of manually doing it?

--

Dave Peterson