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.