Allow user
 

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.

Allow user
 

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 sheet. 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" wrote in message
...
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.

Allow user
 

I should have added that you'll want to password protect the worksheet as
well as the VBA code. To password protect the sheet, 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" wrote in message
...
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 sheet. 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" wrote in message
...
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.

Allow user
 

I think you would need to check who opened the workbook
(Environ("UserName")) in the Woirkbook_Open event, and unlock or lock cells
as required.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"A.G.M ash" wrote in message
...
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.

Allow user
 

Thanks very much chip. You gave me far more than I expected. Its not a big
issue the password protection. the users of the spreadsheet wouldnt know how
to even begin trying to break them. but I will take note of that sowftware. I
might find it useful

will test what you gave and come back if i am having difficulty.

thanks again

"Chip Pearson" wrote:

I should have added that you'll want to password protect the worksheet as
well as the VBA code. To password protect the sheet, 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" wrote in message
...
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 sheet. 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" wrote in message
...
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.

Allow user
 

when I try to implement the code I get the error

compile error

expected: line number or label or statment or end of statement

in this section of the code

( _
ByVal lpBuffer As String, _
nSize As Long) As Long )

do you know the solution to that. I tried fidiling around with it no success

thanks ash

"Chip Pearson" wrote:

I should have added that you'll want to password protect the worksheet as
well as the VBA code. To password protect the sheet, 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" wrote in message
...
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 sheet. 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" wrote in message
...
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.

Allow user
 

Ash, the ( _ belongs on the previous line that begins
Private Declare Function GetUserName Lib

The message split the line and it sounds as though you may not have
stitched it back together.

Alan


A.G.M ash wrote:
when I try to implement the code I get the error

compile error

expected: line number or label or statment or end of statement

in this section of the code

( _
ByVal lpBuffer As String, _
nSize As Long) As Long )

do you know the solution to that. I tried fidiling around with it no success

thanks ash

"Chip Pearson" wrote:

I should have added that you'll want to password protect the worksheet as
well as the VBA code. To password protect the sheet, 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" wrote in message
...
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 sheet. 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" wrote in message
...
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.

Allow user
 

It was a line-break problem in the news post. The line of code got wrapped
where I didn't intend it to. Use the revised formatted version:

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

Note that there must be a space character in front of each _ character.


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




"A.G.M ash" wrote in message
...

when I try to implement the code I get the error

compile error

expected: line number or label or statment or end of statement

in this section of the code

( _
ByVal lpBuffer As String, _
nSize As Long) As Long )

do you know the solution to that. I tried fidiling around with it no
success

thanks ash

"Chip Pearson" wrote:

I should have added that you'll want to password protect the worksheet as
well as the VBA code. To password protect the sheet, 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" wrote in message
...
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 sheet. 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" wrote in message
...
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.