protect and unprotect with VBA

[shyamvinjamuri]

Hi Guys,

Is there a way to protect and unprotect sheets and workbook through VBA?

Here is what I am looking for:

I want to unprotect the workbook and unprotect Sheet1, Sheet4 and Sheet 6, if the user ID is ABC. And, Protect all sheets and workbook on close.

Thanks

 

[Smitty]

How's this for a start:

<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        ws.Protect
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> user <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
        user = Environ("Username")
        
        <SPAN style="color:#00007F">If</SPAN> user = "ABC" <SPAN style="color:#00007F">Then</SPAN>
            Sheets("Sheet1").Unprotect
            Sheets("Sheet4").Unprotect
            Sheets("Sheet6").Unprotect
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[shyamvinjamuri]

Pennysaver,

Thank you so much.