Code to Change Sheet Name

[Sean]

How could I create a UserForm that willchange the Sheet name on the
active sheet? I know this is over complication but I have users that
find this hard to do!

I am looking for the input text to be in PROPER case

Thanks

 

[Chip Pearson]

No need for a user form. Try something like

Sub RenameSheet()
Dim V As Variant
V = Application.InputBox(prompt:="Enter New Name", Type:=2)
If VarType(V) = vbBoolean Then
If V = False Then
Debug.Print "user cancelled"
Exit Sub
End If
End If
On Error Resume Next
If Worksheets(V) Is Nothing Then
ActiveSheet.Name = StrConv(V, vbProperCase)
Else
MsgBox "Sheet already exists"
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Nigel]

An example to get you going.....

Sub Cname()
myname = Trim(InputBox("Enter Name for Active Sheet", "Rename Sheet",
ActiveSheet.Name))
On Error GoTo errHandler
ActiveSheet.Name = Application.WorksheetFunction.Proper(myname)
Exit Sub
errHandler:
MsgBox "Invalid Name"
On Error GoTo 0
End Sub

 

[Sean]

Chip, once again you are a fountain of knowledge, many Thanks. Also to
you Nigel