PROTECTION FOR DIFFERENT COLUMN OF A WORKSHEET
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 -- reply to my posts are welcome |
PROTECTION FOR DIFFERENT COLUMN OF A WORKSHEET
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 "NSNR" wrote: 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 -- reply to my posts are welcome |
All times are GMT +1. The time now is 03:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com