enable macro to disable save option by user

[iamhsn]

hello to everyone.
i have made a book which is in a LAN server and (using shortcut) people using it in different location/store. now to keep the files content accurate, i must have to disable the save/save as function or else they are saving it in their local and then the file doesnt update its external content.. so, to stop all this trouble, i tried to use below codes to make the user either enable the macro option and use it or stop loading the file....

Option Explicit 

Const WelcomePage = "Macros" 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False 
     
     'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook 
        If Not .Saved Then 
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                vbYesNoCancel + vbExclamation) 
            Case Is = vbYes 
                 'Call customized save routine
                Call CustomSave 
            Case Is = vbNo 
                 'Do not save
            Case Is = vbCancel 
                 'Set up procedure to cancel close
                Cancel = True 
            End Select 
        End If 
         
         'If Cancel was clicked, turn events back on and cancel close,
         'otherwise close the workbook without saving further changes
        If Not Cancel = True Then 
            .Saved = True 
            Application.EnableEvents = True 
            .Close savechanges:=False 
        Else 
            Application.EnableEvents = True 
        End If 
    End With 
End Sub 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False 
     
     'Call customized save routine and set workbook's saved property to true
     '(To cancel regular saving)
    Call CustomSave(SaveAsUI) 
    Cancel = True 
     
     'Turn events back on an set saved property to true
    Application.EnableEvents = True 
    ThisWorkbook.Saved = True 
End Sub 

Private Sub Workbook_Open() 
     'Unhide all worksheets
    Application.ScreenUpdating = False 
    Call ShowAllSheets 
    Application.ScreenUpdating = True 
End Sub 

Private Sub CustomSave(Optional SaveAs As Boolean) 
    Dim ws As Worksheet, aWs As Worksheet, newFname As String 
     'Turn off screen flashing
    Application.ScreenUpdating = False 
     
     'Record active worksheet
    Set aWs = ActiveSheet 
     
     'Hide all sheets
    Call HideAllSheets 
     
     'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then 
        newFname = Application.GetSaveAsFilename( _ 
        fileFilter:="Excel Files (*.xls), *.xls") 
        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname 
    Else 
        ThisWorkbook.Save 
    End If 
     
     'Restore file to where user was
    Call ShowAllSheets 
    aWs.Activate 
     
     'Restore screen updates
    Application.ScreenUpdating = True 
End Sub 

Private Sub HideAllSheets() 
     'Hide all worksheets except the macro welcome page
    Dim ws As Worksheet 
     
    Worksheets(WelcomePage).Visible = xlSheetVisible 
     
    For Each ws In ThisWorkbook.Worksheets 
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden 
    Next ws 
     
    Worksheets(WelcomePage).Activate 
End Sub 

Private Sub ShowAllSheets() 
     'Show all worksheets except the macro welcome page
     
    Dim ws As Worksheet 
     
    For Each ws In ThisWorkbook.Worksheets 
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible 
    Next ws 
     
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden 
End Sub

it worked perfectly but this code will still let users save. so, i need to use below code somewhere to restrict people using it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)



    MsgBox "You can't save this workbook!"

    Cancel = True



End Sub

i tried using it but somehow it doesnt work. if it works then the first code doesnt work (macro detect wont wok)

can anyone please help me on this. i am just not that smart to solve it myself so looking for your help please

thank you

 

[baitmaster]

welcome to the board

I'm going to simplify a bit of the logic as I think that will help. From what I can tell,

         'If Cancel was clicked, turn events back on and cancel close,
         'otherwise close the workbook without saving further changes
        If Not Cancel = True Then 
            .Saved = True 
            Application.EnableEvents = True 
            .Close savechanges:=False 
        Else 
            Application.EnableEvents = True 
        End If 
    End With

Can simply become

Application.EnableEvents = True
If Not Cancel = True Then .Close savechanges:=False

The .Saved property will have no impact because you are dictating the savechanges property. If this logic is correct, then given that there is nothing else in the Workbook_BeforeClose routine that will trigger events, the Application.EnableEvents settings can be moved to the CustomSave routine for simplicity / clarity.

That done, I don't understand why you have 2 Workbook_BeforeSave routines, and I think you need to simply lose the existing one. If your goal is to prevent the user saving the file outside of strictly controlled parameters, then why would you have something that allows a pop-up? Events are switched off in the before_close routine, so this won't be triggered there, and if you want it to be, then simply rewrite the logic of this routine into the before_close or custom_save routines