Macro to have user enter new worksheet name

Hello all

At the end of my macro, I want the user to rename the active worksheet. Is
there some way I can do this in VBA.

Failing that, how would I get a message box to appear reminding the user
they need to change the worksheet name.

Thank you in advance.
MMH

One way:

Public Sub RenameSheet()
Dim vNewName As Variant
With ActiveSheet
Do
vNewName = Application.InputBox( _
Prompt:="Enter new worksheet name:", _
Title:="Rename Worksheet", _
Type:=2)
If vNewName = False Then Exit Sub 'user cancelled
If UCase(vNewName) = UCase(.Name) Then
vNewName = ""
ElseIf Len(Trim(vNewName)) > 0 Then
On Error Resume Next
.Name = vNewName
On Error GoTo 0
End If
Loop Until .Name = vNewName
End With
End Sub


In article <[email protected]>,
MMH <[email protected]> wrote:

> At the end of my macro, I want the user to rename the active worksheet. Is
> there some way I can do this in VBA.
>
> Failing that, how would I get a message box to appear reminding the user
> they need to change the worksheet name.

Thank you, that did exactly what I wanted.

MMH

"JE McGimpsey" wrote:

> One way:
>
> Public Sub RenameSheet()
> Dim vNewName As Variant
> With ActiveSheet
> Do
> vNewName = Application.InputBox( _
> Prompt:="Enter new worksheet name:", _
> Title:="Rename Worksheet", _
> Type:=2)
> If vNewName = False Then Exit Sub 'user cancelled
> If UCase(vNewName) = UCase(.Name) Then
> vNewName = ""
> ElseIf Len(Trim(vNewName)) > 0 Then
> On Error Resume Next
> .Name = vNewName
> On Error GoTo 0
> End If
> Loop Until .Name = vNewName
> End With
> End Sub
>
>
> In article <[email protected]>,
> MMH <[email protected]> wrote:
>
> > At the end of my macro, I want the user to rename the active worksheet. Is
> > there some way I can do this in VBA.
> >
> > Failing that, how would I get a message box to appear reminding the user
> > they need to change the worksheet name.
>