Force 'Save As' with new filename - problem with Workbook_BeforeSave

BlissC

New Member
Joined
Aug 28, 2017
Messages
41
I'm trying to set up my workbook so that with the 'blank' copy of the file where the sections the user inputs data are empty, users have to 'Save As' and save the file with a different filename to prevent overwriting of the file so that the blank 'master' file remains blank for when the next user comes along and needs to use the blank file.

I have a problem though because both this code (at the top of the page), and a block of code just a little further down (that for locking a range of cells on the Welcome page before saving the file) both use Workbook_BeforeSave. Obviously though, this is causing a compile error due to the ambiguous name (i.e. Workbook_BeforeSave). Is there a way round this?

Below is all of the VBA I'm using on ThisWorkbook:

VBA Code:
'force user to SAVE AS a new filename to prevent overwriting of blank eLeave card
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Const strRestrictedName As String = "eLeave_v2-0_BLANK.xlsm"
Dim strFileName As String

  Cancel = True
 
TryAgain:
  strFileName = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
  If LCase(strFileName) = "false" Then Exit Sub
  strFileName = Mid$(strFileName, InStrRev(strFileName, "\") + 1)
 
  If UCase$(strFileName) = UCase$(strRestrictedName) Then
    MsgBox "Invalid File Name!" & vbCrLf & vbCrLf & "Saving this file as the BLANK file is not allowed. Please re-name the file", vbCritical, "Stop"
    GoTo TryAgain
  Else
    ActiveWorkbook.SaveAs strFileName
  End If
 
End Sub

'lock cells on Welcome page before CLOSING the file
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Save
End Sub
 
'lock cells on Welcome page before SAVING the file
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim trg As Range
    Dim rng As Range
    With Worksheets("Welcome")
        .Unprotect Password:="password"
        Set trg = Worksheets("Welcome").Range("F23:F29")
        On Error Resume Next
        Set rng = trg.SpecialCells(xlCellTypeConstants)
        If Not rng Is Nothing Then
            rng.Locked = True
        End If
        Set rng = Nothing
        Set rng = trg.SpecialCells(xlCellTypeFormulas)
        If Not rng Is Nothing Then
            rng.Locked = True
        End If
        .Protect Password:="password"
    End With
End Sub

'code for MANAGER and AUTHORISED PEOPLE access
'sets up details of which worksheets to restrict and authorised users
Private Sub Workbook_Open()
    Dim cell As Range
    Dim wsAccess As Worksheet
    Dim ManagerNames As Variant
    Dim IsManager As Boolean
    
'location of data for security - worksheet name
    Set wsAccess = ThisWorkbook.Worksheets("Access")
    
'location of data for security - authorised MANAGERS
    ManagerNames = wsAccess.Range("G9:G18").Value2
    IsManager = Not IsError(Application.Match(Application.UserName, ManagerNames, 0))
  
'location of data for security - which worksheets to restrict
    For Each cell In wsAccess.Range("C9:C12")
        If Len(cell.Value) > 0 Then
            ThisWorkbook.Worksheets(cell.Value).Visible = IsManager
        End If
    Next cell
  
  
'code for EMPLOYEE access
  
    Dim wsAccessE As Worksheet
    Dim EmployeeNames As Variant
    Dim IsEmployee As Boolean
  
'location of data for security - worksheet name
    Set wsAccessE = ThisWorkbook.Worksheets("Access")
      
'location of data for security - authorised EMPLOYEE
    EmployeeNames = wsAccessE.Range("G27:G37").Value2
    IsEmployee = Not IsError(Application.Match(Application.UserName, EmployeeNames, 0))
  
'location of data for security - which worksheets to restrict
    For Each cell In wsAccessE.Range("C27:C28")
        If Len(cell.Value) > 0 Then
            ThisWorkbook.Worksheets(cell.Value).Visible = IsEmployee
        End If
    Next cell
  
End Sub
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,992
Office Version
  1. 2019
Platform
  1. Windows
Obviously though, this is causing a compile error due to the ambiguous name (i.e. Workbook_BeforeSave). Is there a way round this?
Move the code from the second save event into the first one, then delete the second one.

You could make the blank read only so that the original can't be saved without a password to unprotect first.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
Why not just make the master file a read-only version?
That way they will not be able to override the original, and forced to do a "SaveAs".
 

BlissC

New Member
Joined
Aug 28, 2017
Messages
41
Move the code from the second save event into the first one, then delete the second one.

@jasonb75 - Thank you so much! (it never occurred to me that it could be that simple!) That's working great - just what I wanted, except for one thing....

...I have my Excel file, named "eLeave_v2-0_BLANK.xlsm", which is the name I don't want users to save the file as, but when I add in the VBA code to force the 'Save As', it doesn't let me save the file with that name either, so I'm unable to save the new VBA I've added in, or make any modifications to the file without it forcing me to 'Save As' as well, so at the moment I'm unable to save my final version with that filename.

Elsewhere in the file I'm using Application.UserName to restrict access to certain sheets on the workbook. Is it possible to use Application.UserName, or something similar to allow me to save the file with that filename, but make everyone else "Save As"?

@Joe4 - I did consider making the file read-only, but from previous experience with our users using read-only files I tend to get an awful lot of phone calls complaining they can't make any changes to the file. Instructing them to 'Save As' in the user guidance doesn't help, as they don't bother reading that, so it works better when they get a big warning come up telling them exactly what to do/what not to do.

Thanks,

Bliss
 

BlissC

New Member
Joined
Aug 28, 2017
Messages
41
Having done some digging around online, I've found the following VBA code snippet, which I think might do the trick:

VBA Code:
If Application.UserName <> "YourUserName" Then

...but I'm not sure how I'd incorporate it into my existing code. This is the existing code block that I'm using to force the "Save As" and lock a number of cells when the file is saved:

VBA Code:
'force user to SAVE AS a new filename to prevent overwriting of blank eLeave card
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Const strRestrictedName As String = "eLeave_2-1_BLANK.xlsm"
Dim strFileName As String

    Cancel = True
 
TryAgain:
  strFileName = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
  If LCase(strFileName) = "false" Then Exit Sub
  strFileName = Mid$(strFileName, InStrRev(strFileName, "\") + 1)
 
  If UCase$(strFileName) = UCase$(strRestrictedName) Then
  MsgBox "Invalid File Name!" & vbCrLf & vbCrLf & "Saving this file as the BLANK file is not allowed. Please re-name the file", vbCritical, "Stop"
    GoTo TryAgain
  Else
    ActiveWorkbook.SaveAs strFileName
  End If
 
'lock cells on Welcome page before SAVING the file
    Dim trg As Range
    Dim rng As Range
    With Worksheets("Welcome")
        .Unprotect Password:="password"
        Set trg = Worksheets("Welcome").Range("F23:F29")
        On Error Resume Next
        Set rng = trg.SpecialCells(xlCellTypeConstants)
        If Not rng Is Nothing Then
            rng.Locked = True
        End If
        Set rng = Nothing
        Set rng = trg.SpecialCells(xlCellTypeFormulas)
        If Not rng Is Nothing Then
            rng.Locked = True
        End If
        .Protect Password:="password"
    End With
 
End Sub

I'm thinking that it possibly needs to go after the Private Sub Workbook_BeforeSave but I'm not sure if this is the right place, or what else I'd need to put in.

Any advice gratefully received.

Thanks,

Bliss
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,494
Members
410,686
Latest member
Fer9us
Top