Selected Answer
VBA can prevent the user from changing the name of a workbook. However, if the user knows how to disable VBA he can do whatever he wants. VBA is enabled by default when Excel starts. Frankly, I wouldn't draw the user's attention to the fact that he can sabotage VBA just before I use VBA to restrict his actions. Therefore I find the initial command to "Enable Macros" counter-productive.
I have modified your code in the 'ThisWorkbook' module to make it more readable and improve the functionality. Please replace the code you have with this one.
Option Explicit
Private Sub Workbook_Open()
' 30 Jan 2018
With Worksheets("1")
.Visible = True
.Activate
End With
'worksheets to hide on startup
Sheets("2").Visible = xlSheetVeryHidden
Sheets("3").Visible = xlSheetVeryHidden
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' 30 Jan 2018
If SaveAsUI Then
MsgBox "You may not change the name of this workbook." & vbCr & _
"Please close the workbook retaining its original name.", _
vbExclamation, "Illegal action"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' 31 Jan 2018
With ActiveWorkbook
If Not .Saved Then
Application.DisplayAlerts = False
.Close SaveChanges:=True
Application.DisplayAlerts = True
End If
End With
' All of this is done when you open the workbook:
'worksheets to show when macro is enabled
' Sheets("1").Visible = True
' Sheets("2").Visible = xlSheetVeryHidden
' Sheets("3").Visible = xlSheetVeryHidden
' ActiveWorkbook.Close ' This proc was called because Excel is
' already closing the workbook.
' Application.Quit ' Don't Quit an application using code
' which you didn't create using code.
End Sub
One particular logic I like to highlight for you is the fact that, in Excel, you can't save the workbook by another name unless you use the 'SaveAs' command. The 'Workbook_BeforeSave' event procedure can detect that and cancel the Save action if it was initiated using 'SaveAs'.
