How to disable save with the same name

[Nelson78]

Hello.

I need a help to prevent other people saving on the master file.

The approach is the following:
1) Clicking on the button "Save", I need the pop-up "You're saving with the same name" and stop the operation;
2) Clicking on the button "Save as" without changing the name, then pop-up "You're saving with the same name" and stop the operation;
3) Clicking on the button "Save as" changing the name, it's ok;
4) Set a sort of secret combination of buttons to let me saving (example: ctrl+s)

Thank you.

 

[mumps]

Try this macro in the code module for Thisworkbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As String
    Application.EnableEvents = False
    If SaveAsUI = False Then
        MsgBox "You can't save this workbook with the same name!"
        Cancel = True
    Else
        fName = InputBox("Enter a name for the file including the extension that is different from " & ThisWorkbook.Name)
        If Len(fName) <> Len(ThisWorkbook.Name) And Left(fName & ".xlsm", Len(fName)) <> Left(ThisWorkbook.Name, Len(fName)) Then
            ThisWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & fName & ".xlsm"
            MsgBox ("The file has been saved with the name: " & fName)
            Cancel = True
        Else
            If InStr(1, fName, ".xlsm") = 0 Or Left(fName & ".xlsm", Len(fName)) = Left(ThisWorkbook.Name, Len(fName)) Then
                MsgBox ("You must change the file name" & Chr(10) & "and you must include the extension in the file name.")
                Cancel = True
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[Nelson78]

Thank's for the answer.

Two problems:
1) I've not the opportunity of saving with the same name (ctrl+s doesn't work);
2) when the file is renamed, the user should have the possibility of choosing where to save.

 

[jkpieterse]

Why not set a password to modify? File, Save-as, press Tools dropdown and choose General Options, enter a password next to "Password to modify". On top of that, also check the "Read-Only recommended box.

 

[Nelson78]

Why not set a password to modify? File, Save-as, press Tools dropdown and choose General Options, enter a password next to "Password to modify". On top of that, also check the "Read-Only recommended box.

I can't find Tools dropdown.

I'm working with Microsoft Office Excel 2007.

 

[mumps]

This version will allow you to choose where to save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As String
    Dim sPath As String
    Application.EnableEvents = False
    If SaveAsUI = False Then
        MsgBox "You can't save this workbook with the same name!"
        Cancel = True
    Else
        fName = InputBox("Enter a name for the file including the extension that is different from " & ThisWorkbook.Name)
        If Len(fName) <> Len(ThisWorkbook.Name) And Left(fName & ".xlsm", Len(fName)) <> Left(ThisWorkbook.Name, Len(fName)) Then
            With Application.FileDialog(msoFileDialogFolderPicker)
                .AllowMultiSelect = False
                .Show
                If .SelectedItems.Count > 0 Then
                    sPath = .SelectedItems(1)
                    ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & fName & ".xlsm"
                    MsgBox ("The file has been saved with the name: " & fName)
                    Cancel = True
                End If
            End With
        Else
            If InStr(1, fName, ".xlsm") = 0 Or Left(fName & ".xlsm", Len(fName)) = Left(ThisWorkbook.Name, Len(fName)) Then
                MsgBox ("You must change the file name" & Chr(10) & "and you must include the extension in the file name.")
                Cancel = True
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

There is a simple way that will allow you to save the file with the same name. Click the 'Developer' tab in the menu at the top. If it isn't visible, you can make it visible by right clicking any existing tab in the menu at the top and then clicking 'Customize the ribbon'. In the right hand pane, put a check mark in the box to the left of 'Developer' and click 'OK". In the 'Developer' tab 'Controls' group, click the 'Design Mode' button. It will change color. This temporarily disables the macros. You will now be able to save the file with the same name or any other name. After saving, click the 'Design Mode' button again to re-enable the macros. The button acts as an on-off switch.

 

[jkpieterse]

Click Save_As, there should be a Tools button on the save-as window that pops open. Probably bottom-left corner.

 

[Nelson78]

This version will allow you to choose where to save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As String
    Dim sPath As String
    Application.EnableEvents = False
    If SaveAsUI = False Then
        MsgBox "You can't save this workbook with the same name!"
        Cancel = True
    Else
        fName = InputBox("Enter a name for the file including the extension that is different from " & ThisWorkbook.Name)
        If Len(fName) <> Len(ThisWorkbook.Name) And Left(fName & ".xlsm", Len(fName)) <> Left(ThisWorkbook.Name, Len(fName)) Then
            With Application.FileDialog(msoFileDialogFolderPicker)
                .AllowMultiSelect = False
                .Show
                If .SelectedItems.Count > 0 Then
                    sPath = .SelectedItems(1)
                    ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & fName & ".xlsm"
                    MsgBox ("The file has been saved with the name: " & fName)
                    Cancel = True
                End If
            End With
        Else
            If InStr(1, fName, ".xlsm") = 0 Or Left(fName & ".xlsm", Len(fName)) = Left(ThisWorkbook.Name, Len(fName)) Then
                MsgBox ("You must change the file name" & Chr(10) & "and you must include the extension in the file name.")
                Cancel = True
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

There is a simple way that will allow you to save the file with the same name. Click the 'Developer' tab in the menu at the top. If it isn't visible, you can make it visible by right clicking any existing tab in the menu at the top and then clicking 'Customize the ribbon'. In the right hand pane, put a check mark in the box to the left of 'Developer' and click 'OK". In the 'Developer' tab 'Controls' group, click the 'Design Mode' button. It will change color. This temporarily disables the macros. You will now be able to save the file with the same name or any other name. After saving, click the 'Design Mode' button again to re-enable the macros. The button acts as an on-off switch.

I'm testing your suggestion: it seems to serve very well.
Thank's.

 

[Nelson78]

Click Save_As, there should be a Tools button on the save-as window that pops open. Probably bottom-left corner.

Ok, found. I had it in front of my eyes but I didn't see it.
Later I'm going to test it.

Thank's.

 

[mumps]

You are very welcome.