Save & Rename Worksheet

[GregT7976]

I know how to save a worksheet to the bar below. Being a little lazy and inquisitive is there a way to use a button, with a macro to achieve the same thing?

Thanks

[Artik]

Sub RenameActivesheet()
    Dim strNewName  As String
    Dim Sh          As Object
    Dim blnDup      As Boolean

    Do
        strNewName = InputBox("Enter the new sheet name:", "New name", ActiveSheet.Name)

        If StrPtr(strNewName) = 0 Then
            Exit Do    'Pressed Cancel
        Else
            For Each Sh In ActiveWorkbook.Sheets
                If LCase(strNewName) = LCase(Sh.Name) Then
                    If LCase(strNewName) <> LCase(ActiveSheet.Name) Then
                        blnDup = True
                        MsgBox "A sheet named '" & strNewName & "' already exists. " & _
                               "Choose a different name.", vbExclamation
                        Exit For
                    End If
                End If
            Next Sh

            If Not blnDup Then
                ActiveSheet.Name = strNewName
                Exit Do
            End If
        End If
    Loop
End Sub

Or

Sub RenameActivesheet1()
    Dim strNewName  As String
    Dim Sh          As Object
    Dim blnDup      As Boolean

    Do
        strNewName = InputBox("Enter the new sheet name:", "New name", ActiveSheet.Name)

        If StrPtr(strNewName) = 0 Then
            Exit Do    'Pressed Cancel
        Else
            If IsSheetExists(ActiveWorkbook, strNewName) And LCase(strNewName) <> LCase(ActiveSheet.Name) Then
                blnDup = True
                MsgBox "A sheet named '" & strNewName & "' already exists. " & _
                       "Choose a different name.", vbExclamation
            Else
                ActiveSheet.Name = strNewName
                Exit Do
            End If
        End If
    Loop
End Sub


Function IsSheetExists(wkb As Workbook, strShName As String) As Boolean
    Dim oSh         As Object

    On Error Resume Next
    Set oSh = wkb.Sheets(strShName)
    On Error GoTo 0

    IsSheetExists = Not (oSh Is Nothing)
End Function

Macros do not (or rather, they should) include validation of the new name, because the sheet name has its limitations.

Artik

[GregT7976]

Hi in your second reply at

Do
strNewName = InputBox("Enter the new sheet name:", "New name", ActiveSheet.Name) can I just enter the cell name "B3" at "Enter the new sheet name:"

[Artik]

Here's another suggestion by Mumps1 from another thread:

Sub RenameSheet()
    Dim response As String
    response = InputBox("Please enter the new name of the sheet.")
    If response = "" Then Exit Sub
    If Not Evaluate("isref('" & response & "'!A1)") Then
       ActiveSheet.Name = response
    Else
        MsgBox ("A sheet named '" & response & "' already exists." & Chr(10) & "Please try again using a different name.")
    End If
End Sub

Artik

[GregT7976]

thanks everyone

[Artik]

I don't know if I understood correctly.
Perhaps

strNewName = InputBox(Range("B3").Value, "New name", ActiveSheet.Name)

Artik