test username on workbook open unlock all sheets

[ADAMC]

Hi all,

I have a rough idea of what I want but my structure is confusing me big time

Mixing if/ then/ else with For each/ next aaaaaaarghhhhhhhh

what i want to do:
IF username is "me" unlock every sheet in the workbook and show a msgbox
if its anyone but me leave it as is and just open it

what I have

Private Sub Workbook_Open()
If Environ("Username") = "EMPLOYEENAME" Then
'Unlock all the worksheets 
   MsgBox "Logged in as an admin worksheet fully accessible"
Else
'Just open as normal with sheet protection active
End If
End Sub

Can someone help me with the unprtect all worksheets bit?

I was along the lines of:

For Each wSheet In Worksheets 
        wSheet.Unprotect Password:= "" 
    Next wSheet

but couldnt seem to incorporate the 2 together.

On the workbook close event again id need to re test to say if being closed by the admin re - protect all worksheets again.......

Thanks

 

[VoG]

Try like this

Private Sub Workbook_Open()
Dim ws As Worksheet
If Environ("Username") = "EMPLOYEENAME" Then
'Unlock all the worksheets
   MsgBox "Logged in as an admin worksheet fully accessible"
   For Each ws In Me.Worksheets
        ws.Unprotect Password:="abc"
    Next ws
Else
'Just open as normal with sheet protection active
End If
End Sub

 

[ADAMC]

VOG - Thank you...I guess I was almost there...........(Apologies)

I assume I can then just replicate on the close event:

Private Sub Workbook_Close()
Dim ws As Worksheet
If Environ("Username") = "EMPLOYEENAME" Then
'Unlock all the worksheets
   MsgBox "Logged in as an admin worksheet fully accessible"
   For Each ws In Me.Worksheets
        ws.Protect Password:="abc"
    Next ws
Else
'Just open as normal with sheet protection active
End If
End Sub

Also would you happen to know any good links where I can learn about code structure/ best practice.

Would like to try an learn about:
Select case
With end With
etc......


Thanks!

 

[VoG]

Your workbook close code looks fine.

Take a look at Hiker's List http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[ADAMC]

Your workbook close code looks fine.

Take a look at Hiker's List http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

Brilliant Ill see how it works out.

Thank you!