Protect All Worksheets Before Closing The Workbook

**rich_cirillo** · 07-13-2013, 07:46 PM

Hi

I require a code when closing workbook.When closing workbook that all sheets are in protect mode.
Also I have a commandbutton1 in sheet Weekly Graph and a commandbutton1 in Score Sheet. When the workbook is closed and all sheets are protected the 2 commandbuttons say Unprotect Sheet.When I then open the workbook the commandbutton1 in Weekly Graph says Unprotect Sheet and the commandbutton1 in Score sheet says Unprotect Sheet.

The 2 commandbuttons have the captions Protect Sheet and Unprotect Sheet

Thanks

**bakerman** · 07-14-2013, 03:21 AM

Do you try anything yourself at all ??
You've just been given a code to protect all sheets on opening the workbook. Use the same code but this time in the Workbook_BeforeClose-event.

**rich_cirillo** · 07-14-2013, 07:01 AM

I don`t remember asking for your help specificly...there are far more clever people on here then you......it is pretty obvious i do not know anything about code....

**bakerman** · 07-14-2013, 11:26 AM

Then I advise you to get a good book about VBA so that you at least understand the basics of what is written here.

there are far more clever people on here then you

How can you evaluate that since you don't know anything about code ????

**Excel Fox** · 07-14-2013, 01:32 PM

rich, whether you know code or not, disregard to another member will not be entertained. baker only implied that if you do a trial and error, you will learn something yourself, and not have to rely on the forum for every aspect of coding, and only ask things that are beyond your comprehension. anyway, I for one do not mind anyone here asking questions that are seemingly straightforward or plain simple, however, we at the forum do have issues with targeted comments that are a little too personal in nature. If such an offense is repeated, your posting privileges in this forum may be revoked, temporarily or permanently, depending on the offense.

**Excel Fox** · 07-14-2013, 01:39 PM

And here's what you're looking for

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim sht As Object
    Dim blnSaved As Boolean
    
    blnSaved = ThisWorkbook.Saved
    For Each sht In ThisWorkbook.Sheets
        sht.Unprotect Password:="Password"
        sht.Protect Password:="Password", UserInterfaceOnly:=True
    Next sht
    Worksheets("Weekly Graph").CommandButton1.Caption = "Unprotect Sheet"
    Worksheets("Score Sheet").CommandButton1.Caption = "Unprotect Sheet"
    If blnSaved Then
        ThisWorkbook.Save
    End If
    
End Sub

**rich_cirillo** · 07-14-2013, 01:47 PM

First thanks for the code.I have never personally attacked anyone here before and have thanked anyone for there help...I have no issues with users saying find a good VBA book to learn the basics which is a fair call but I find this comment personal as well which is why I responded - Do you try anything yourself??? ....People read things in different ways

I did have a code which did work for 1 sheet but I could not get it to work for multiple sheets.

I accept your decision to ban my membership if required

Thanks

Rich

**Excel Fox** · 07-14-2013, 10:00 PM

baker could probably have rephrased that line as a more indirect one, still implying what was intended. having said that, rich, you didn't have to revert with the comment targeting baker in response to his question of trying things yourself. yes people read things differently, but to me, rich's was a more personal assault than anything.

anyway, I'll be keeping a watch. any defense to your action in this specific incident will be considered as an offense.

closing the thread here as this conversation is over. period.