Change Worksheet Names by Function/VBA? (2000)

Hello everyone. Does anyone know if it is possible to use a function or VBA code to dynamically change the name of a worksheet tab (you know, the tabs at the bottom of excel)? Please let me know if anyone has discovered a way to do this, as I can't seem to find anything out there... Thank you for your time and insight!

Re: Change Worksheet Names by Function/VBA? (2000)

Thank you for that info - much appreciated. How could I rename a worksheet that I am currently using. For example, the worksheet is called "Sheet 4" but depending on what the user inputs into a certain cell, I would like to rename the current worksheet to something else. Thank you for your feedback...!!!

Re: Change Worksheet Names by Function/VBA? (2000)

or with a dialog box
<pre>Sub RenameSheet()
Dim Newname As String
Newname = InputBox("Name for new worksheet?")
If Newname <> "" Then
ActiveSheet.Name = Newname
End If
End Sub
</pre>

If you want some error checking to make sure it is valid
<pre>Sub RenameSheet2()
On Error Resume Next

ActiveSheet.Name = InputBox("Name for new worksheet?")

'Keep asking for name if name is invalid
Do Until Err.Number = 0
Err.Clear
ActiveSheet.Name = InputBox("Try Again!" _
& vbCrLf & "Invalid Name or Name Already Exists" _
& vbCrLf & "Please name the New Sheet")
Loop
On Error GoTo 0

Re: Change Worksheet Names by Function/VBA? (2000)

If you want to do it "automagically", you should use a Worksheet_Change event. See Sams <post#=164155>post 164155</post#> for how to set up a Worksheet_Change event, and Legare's example code for another purpose in this <post#=163547>post 163547</post#>, and Steve's post in this thread. You should end up with something like this:

Re: Change Worksheet Names by Function/VBA? (2000)

Just a note and opinion:
Much of the code for changing the name posted, does NOT have any error checking. Renaming a sheet is more prone to have an error than many other types of code you might create:

Names must be <32 characters long
They can NOT be blank
They can NOT contain any of the following:
/ ? * [ ]

You should at least "trap the error", keep the old name, and send a message to the user.

I would NOT want my users getting a "crashed" macro, especially when you are automating a task that is essentially a beginner's level task (dbl-click on the tab and type a new name).

Re: Change Worksheet Names by Function/VBA? (2000)

Of course I agree with your remarks.

I tend to write replies with the idea that the code will be used by the poster him/herself. Error checking is less important in those situations.
Only when they get back to me with remarks like "in this-or-that situation the code errors out, why? " I dive into error checking.