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.

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 D110
'''''''''''''''''''''''''''''''''''
ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked = False
ThisWorkbook.Worksheets(SHEET_NAME).Range("D110").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" <(E-Mail Removed)> wrote in message
news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
>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.
>
>

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" <(E-Mail Removed)> wrote in message
news:uO9V55%(E-Mail Removed)...
> 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 D110
> '''''''''''''''''''''''''''''''''''
> ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked = False
> ThisWorkbook.Worksheets(SHEET_NAME).Range("D110").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" <(E-Mail Removed)> wrote in message
> news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
>>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.
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
> news:uO9V55%(E-Mail Removed)...
> > 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 D110
> > '''''''''''''''''''''''''''''''''''
> > ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked = False
> > ThisWorkbook.Worksheets(SHEET_NAME).Range("D110").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" <(E-Mail Removed)> wrote in message
> > news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
> >>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.
> >>
> >>
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:uO9V55%(E-Mail Removed)...
> > 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 D110
> > '''''''''''''''''''''''''''''''''''
> > ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked = False
> > ThisWorkbook.Worksheets(SHEET_NAME).Range("D110").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" <(E-Mail Removed)> wrote in message
> > news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
> >>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.
> >>
> >>
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:uO9V55%(E-Mail Removed)...
> > > 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 D110
> > > '''''''''''''''''''''''''''''''''''
> > > ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked = False
> > > ThisWorkbook.Worksheets(SHEET_NAME).Range("D110").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" <(E-Mail Removed)> wrote in message
> > > news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
> > >>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.
> > >>
> > >>
> > >
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:741EE7ED-D0EB-4239-8748-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
>> news:uO9V55%(E-Mail Removed)...
>> > 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 D110
>> > '''''''''''''''''''''''''''''''''''
>> > ThisWorkbook.Worksheets(SHEET_NAME).Range("B1:B10").Locked =
>> > False
>> > ThisWorkbook.Worksheets(SHEET_NAME).Range("D110").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" <(E-Mail Removed)> wrote in message
>> > news:351619E0-5DF1-4535-8D05-(E-Mail Removed)...
>> >>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.
>> >>
>> >>
>> >
>> >
>>
>>
>>