Re: Allow user

I should have added that you'll want to password protect the work*** as
well as the VBA code. To password protect the ***, change

ThisWorkbook.Worksheets(SHEET_NAME).Protect UserInterfaceOnly:=True
to
ThisWorkbook.Worksheets(SHEET_NAME).Protect UserInterfaceOnly:=True, _
password:="MyPassword"

To protect the VBA code, in the VBA Editor go to the Tools menu, choose
"VBAProject Properties", select the "Protection" tab, check "Lock Project
For Viewing" and enter a password (twice). Save and close the workbook.

Note that password protection in Excel is notoriously weak. There are any
number of programs that can break the passwords. I use XLKey and VBAKey from
Passware ($50 at http://www.lostpassword.com/) that will break passwords in
a matter of seconds.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



"Chip Pearson" <chip@xxxxxxxxxxxx> wrote in message
news:uO9V55%23AHHA.3560@xxxxxxxxxxxxxxxxxxxxxxx
The functionality you describe is built into Excel 2002 and later. To
implement it in Excel 2000, you could use the following code. Paste all of
the following code in the ThisWorkbook code module in your workbook.

Option Explicit
Option Compare Text

Private Declare Function GetUserName Lib "advapi32.dll" Alias
"GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long

Private Sub Workbook_Open()

Dim UName As String
Dim UNameLen As Long
Dim Res As Long
Const SHEET_NAME = "Sheet1" '<<<< CHANGE AS REQUIRED

''''''''''''''''''''''''''''''''''''
' Initialize the variables.
''''''''''''''''''''''''''''''''''''
UName = String$(255, vbNullChar)
UNameLen = Len(UName)
Res = 0
'''''''''''''''''''''''''''''''''''
' Get the user's network logon name
'''''''''''''''''''''''''''''''''''
Res = GetUserName(UName, UNameLen)
If Res = 0 Then
''''''''''''''''''''''''''''''
' an error occcurred. leave
' all cells locked and get
' out.
''''''''''''''''''''''''''''''
MsgBox "A system error occurred with GetUserName: " _
& CStr(Err.LastDllError)
Exit Sub
End If
'''''''''''''''''''''''''''''''''
' trim the UName string to
' UNameLen-1 characters.
' The -1 is to remove the
' trailing vbNullChar inserted
' by GetUserName.
'''''''''''''''''''''''''''''''''
UName = Left(UName, UNameLen - 1)
'''''''''''''''''''''''''''''''''
' Lock all cells.
'''''''''''''''''''''''''''''''''
Worksheets("Sheet1").Cells.Locked = True
'''''''''''''''''''''''''''''''''
' See who is using the workbook
'''''''''''''''''''''''''''''''''
Select Case UName
Case "User1"
'''''''''''''''''''''''''''''''''''
' User1 is allowed to edit
' A1:A10 and C1:C10
'''''''''''''''''''''''''''''''''''
ThisWorkbook.Worksheets(SHEET_NAME).Range("A1:A10").Locked = False
ThisWorkbook.Worksheets(SHEET_NAME).Range("C1:C10").Locked = False
Case "User2"
'''''''''''''''''''''''''''''''''''
' User2 is allowed to edit
' B1:B10 and D1:D10
'''''''''''''''''''''''''''''''''''
ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked = False
ThisWorkbook.Worksheets(SHEET_NAME).Range("D1:D10").Locked = False
Case "User3"
''''''''''''''''''''''''''''''''''
' Add other user names, unlocking
' the appropriate ranges for that user.
''''''''''''''''''''''''''''''''''
Case Else
''''''''''''''''''''''''''''''''''
' Unexpected user. Leave all cells
' locked.
''''''''''''''''''''''''''''''''''
End Select

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Protect the ***. UserInterfaceOnly:=True allows VBA
' code to change any cell, locked or not, but prevents
' changes by the user.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
ThisWorkbook.Worksheets(SHEET_NAME).Protect UserInterfaceOnly:=True

End Sub

"A.G.M ash" <AGMash@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:351619E0-5DF1-4535-8D05-946E0D7B48AA@xxxxxxxxxxxxxxxx
I am wondering if anyone can help I am using microsoft excel 2000. I have
a
workshhet that has proteceted cells. now I want to allow certain cells to
be
protected for one user and not for for another. However does not have
this
functionality built in is there anyway to create it. Also excell does not
allow you to create a user list with each user having its own pasword to
access the workbook and then obviously to reckognise what rights and
ranges
this user can perform/edit. So the question is can it be done at all.






.

Quantcast