PROTECTION FOR DIFFERENT COLUMN OF A WORKSHEET

[NSNR]

I have created a format(template) in a worksheet. This is been shared on the
network among the users(LAN). I want users to enter the data(dates) w.r.t.
completion of their processes. Each user(dept) has been assigned few columns
to enter these data for corresponding projects in the rows.

I wish to restrict entry to required(necessary) columns to each user, so
that he may not be able to alter data of other user. This I thought of
having different password protections to columns of as many users.

My query is can this be executed in excel assigning different passwords to
diff. columns or is there any other solution for this.

Regards

NSNRao

 

[JLatham]

While each worksheet can only have a single password, you can use assigned
"access codes" (passwords) to then control which columns on a sheet are
accessible to them.

Here are two possible solutions. Both are executed when the workbook is
opened. To put the code in the correct place, open the workbook and press
[Alt]+[F11] to open the VB Editor. Find the "ThisWorkbook" object name in
the VBAProject window and double-click it to open the code module for it.
Copy one of the two Subs below and paste it into the code module and make any
changes needed to it.

In both of the code samples below, the user's name/password is treated like
a password: that is, the test to validate it is case sensitive, so "Bill" is
not the same as "bill" or "BILL". Also, with the second solution you can
have more complex passwords or even multiple word phrases.

This first one requires more maintenance, as it is individual user oriented.
You will need to find out the the Windows login name of each user and change
the code lines starting with Case Is = .... to deal with each one.

Private Sub Workbook_Open()
'change these two values as required
Const whatSheetName = "Sheet1"
Const thePassword = "sheetPW"
'variables used
Dim colToUnlock As String
'begin by unprotecting the sheet
Worksheets(whatSheetName).Unprotect _
Password:=thePassword
'lock all cells!
Worksheets(whatSheetName).Cells.Locked = True
'unlock a column based on the user's
'login name
Select Case Application.UserName
Case Is = "BSmith", "JBrown"
colToUnlock = "E:E"
Case Is = "JLatham"
colToUnlock = "C:C"
Case Is = "NSNRao"
colToUnlock = "G:G"
Case Else
'all other users
colToUnlock = ""
End Select
If colToUnlock = "" Then
MsgBox "You are not authorized to modify this workbook."
Else
Worksheets(whatSheetName). _
Columns(colToUnlock).Locked = False
End If
'put the sheet back into protected state
Worksheets(whatSheetName).Protect _
Password:=thePassword
End Sub

This one is easier to maintain. You give the same "password" to all users
who have access to a particular column. So it is kind of like a group
password. When the workbook is opened they are asked to enter that
code/phrase and access is set up based on their entry.

Private Sub Workbook_Open()
'change these two values as required
Const whatSheetName = "Sheet1"
Const thePassword = "sheetPW"
'variables used
Dim colToUnlock As String
Dim whichUser As String
'begin by unprotecting the sheet
Worksheets(whatSheetName).Unprotect _
Password:=thePassword
'lock all cells!
Worksheets(whatSheetName).Cells.Locked = True
'get the user's name/"password"
whichUser = InputBox("Enter your assigned access code", _
"Access Password Entry", "")
'set up to unlock a column based on
'the user's login name
Select Case whichUser
Case Is = "AllColEUsers"
colToUnlock = "E:E"
Case Is = "AllColCUsers"
colToUnlock = "C:C"
Case Is = "AllColGUsers"
colToUnlock = "G:G"
Case Else
'all other users
colToUnlock = ""
End Select
If colToUnlock = "" Then
MsgBox "You are not authorized to modify this workbook."
Else
'actually unlock the permitted column
Worksheets(whatSheetName). _
Columns(colToUnlock).Locked = False
End If
'put the sheet back into protected state
Worksheets(whatSheetName).Protect _
Password:=thePassword
End Sub