Actually I needed the Workbook_Open event, and had to code it (figure
it out myself, couldn't find info on this, just a bit on protection)

I'm using a UserForm to ask for the password (it's name's PassForm
called on the Workbook_Open event.

I defined 4 ranges, each range start has a particular name followe
with a S (RegistroS for example) and the range end ends with an
(RegistroE), the reason I named the worksheet columns like this i
that, in case adding columns were necessary, the code wouldn't have t
change.

Here's the PassForm code:



Function WorksheetAccess(Area)
Dim sh As Worksheet
Dim SanLocked, RegLocked, CarLocked, MinLocked As Boolean
Dim TextoSaludo As String
Select Case Area
Case "san"
SanLocked = False
RegLocked = True
CarLocked = True
MinLocked = True
TextoSaludo = "Usted tiene acceso al área de Saneamiento"
Case "reg"
SanLocked = True
RegLocked = False
CarLocked = True
MinLocked = True
TextoSaludo = "Usted tiene acceso al área de Registro"
Case "car"
SanLocked = True
RegLocked = True
CarLocked = False
MinLocked = True
TextoSaludo = "Usted tiene acceso al área de Cartera"
Case "min"
SanLocked = True
RegLocked = True
CarLocked = True
MinLocked = False
TextoSaludo = "Usted tiene acceso al área de Minutación"
Case "sis"
SanLocked = False
RegLocked = False
CarLocked = False
MinLocked = False
TextoSaludo = "Usted tiene acceso total"
Case Else
SanLocked = True
RegLocked = True
CarLocked = True
MinLocked = True
TextoSaludo = "Usted no tiene permiso para editar est
documento"
End Select
For Each sh In ThisWorkbook.Worksheets
With sh
.Activate
.Unprotect "JUANCHO"
.Range("SaneamS,SaneamE").Locked = SanLocked
.Range("RegistroS,RegistroE").Locked = RegLocked
.Range("CarteraS,CarteraE").Locked = CarLocked
.Range("MinutacionS,MinutacionE").Locked = MinLocked

.EnableAutoFilter = True
If Area < "sis" Then
.Protect Password:="JUANCHO", UserInterfaceOnly:=True
Application.CommandBars("tools").Controls("Macro") .Enabled = False
Application.CommandBars("tools").Controls("compart i
libro...").Enabled = False
Application.CommandBars("tools").Controls("control d
cambios").Enabled = False
Application.CommandBars("tools").Controls("protege r").Enabled
False
End If
End With

Next
PassForm.Hide
MsgBox TextoSaludo
End Function



Private Sub PassButton_Click()
Select Case TextBox1.Text
Case "1"
WorksheetAccess ("san")
Case "2"
WorksheetAccess ("reg")
Case "3"
WorksheetAccess ("car")
Case "4"
WorksheetAccess ("min")
Case "sistemas"
WorksheetAccess ("sis")
Case Else
MsgBox "Contraseña incorrecta"
TextBox1.SetFocus
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)

End Select

End Sub


Private Sub UserForm_Terminate()
WorksheetAccess ("none")
End Sub



Now, this is strange indeed, I tried wit
ThisWorkbook.Application.CommandBars but it affects other exce
workbooks as well, So I enable again on the beforeclose event...

If you've got any suggestions to improve this code I'd appreciat
them.

Hope this can be of use

--
Message posted from http://www.ExcelForum.com