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
Below is all of the VBA I'm using on ThisWorkbook:
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
