CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 503

Auto protect Excel Workbook on open: ProtectSharing prompt causing hang on code execution

I designed the following VBA code to run on open to check that my workbook's protection is active and if it is not then apply protection to my sheets and workbook:

    If Not ActiveWorkbook.ProtectWindows Or ActiveWorkbook.ProtectStructure Then
        For Each WS In ThisWorkbook.Worksheets
            If WS.ProtectContents = False Then WS.Protect Password:="Password"
        Next WS
        ThisWorkbook.ProtectSharing Password:="Password", SharingPassword:="Password"
    End If

It runs normally without any hiccups except that when it comes to enabling Protection and Sharing for the workbook it will bring up a message stating the file already exists and asks if I want to overwrite it.

If I interact with the prompt and tell it to overwrite the file it acts as expected but I do not want to have to interact with the prompt.

I tried to suppress the prompt with

    Application.DisplayAlerts = False

But this causes the application to hang on open. My guess is the prompt is hidden and is not automatically saving the file as it normally would with a save as prompt. How do I force it to save the file and overwrite existing files so it will not hang at that point?

Upvotes: 0

Views: 375

Answers (1)

Matt
Matt

Reputation: 503

It Turns out that I had to place the Application.DisplayAlerts code right above and below the code in question I was placing it at the start of the routine and before the end which should have worked I do not know why this worked instead. 

If (Not ActiveWorkbook.ProtectWindows) Or (Not ActiveWorkbook.ProtectStructure) Then
    For Each WS In ThisWorkbook.Worksheets
        If WS.ProtectContents = False Then WS.Protect Password:="Password"
    Next WS
    Application.DisplayAlerts = False
    ThisWorkbook.ProtectSharing SharingPassword:="Password"
    Application.DisplayAlerts = True
End If

Upvotes: 1

Related Questions