Display filename in input box and save

[Shadkng]

Hi, after searching online I see there are ways to have a macro bring up an input box where a filename can be entered and saved. Is there a way to have the input box display the current filename so I can just edit the name and save as? This would avoid having to type long filenames. The path would remain the same Additionally, if the filename is not edited can the save be nullified in order not to overwrite it the original file? Thanks very much.

 

[Yongle]

Add a button and put this code behind it

Private Sub ButtonName_Click()
    Dim fName As String
    With ThisWorkbook
        fName = Left(.Name, (InStrRev(.Name, ".") - 1))
        On Error Resume Next
        .SaveAs .Path & "\" & InputBox("Enter new name ...", "Save As", fName)
        On Error GoTo 0
    End With
End Sub

NOTE : above gives the user the option not to overwrite the file if existing name used

 

[Yongle]

To prevent user saving the file with the CURRENT file name ...

Private Sub ButtonName_Click()
    Dim fName As String, newName As String
    With ThisWorkbook
        fName = Left(.Name, (InStrRev(.Name, ".") - 1))
        newName = InputBox("Enter new name ...", "Save As", fName)
        If Not newName = fName Then
            On Error Resume Next
            .SaveAs .Path & "\" & newName
            On Error GoTo 0
        Else
            MsgBox "cannot overwrite current file name", vbExclamation, "oops"
        End If
    End With
End Sub

NOTE :
- Above allows user to overwrite other files if name already exists in the folder - the standard warning appears
- Save and SaveAs options work as normal

Q Do you want to prevent the standard Excel Save button from allowing the file to be saved under current name?

 

[Shadkng]

To prevent user saving the file with the CURRENT file name ...

Private Sub ButtonName_Click()
    Dim fName As String, newName As String
    With ThisWorkbook
        fName = Left(.Name, (InStrRev(.Name, ".") - 1))
        newName = InputBox("Enter new name ...", "Save As", fName)
        If Not newName = fName Then
            On Error Resume Next
            .SaveAs .Path & "\" & newName
            On Error GoTo 0
        Else
            MsgBox "cannot overwrite current file name", vbExclamation, "oops"
        End If
    End With
End Sub

NOTE :
- Above allows user to overwrite other files if name already exists in the folder - the standard warning appears
- Save and SaveAs options work as normal

Q Do you want to prevent the standard Excel Save button from allowing the file to be saved under current name?

 

[Shadkng]

Hi Yongle, both programs ran fine and as you explained. I have to incorporate one of them into my macros. I'm sure it will be fine...I will come back to you if I need any additional help
Thanks a lot for your help.