#1
|
|||
|
|||
How to Control Users to edit
Hi Experts,
Need your help pls I have an excel with below info in A, B, C & D columns A B C D Sulphonator InvoiceNumber Ext.Qty Req.Qty Anil 12345 100 Kumar 34567 78 Sam 93748 56 Anil 823423 12 Sam 8918 786 My requirement is that i have placed this excel on shared drive and given access to users mentioned in column A. If Anil opens the workbook he should be only allowed to edit column D where his name is in column A and reset cells should be locked / should not allow to be edited. Ex: Anil name is seen in Sulphonator column in line 1 & 4 so he should only be able to enter Req.Qty in row 1 & 4. TIA |
#2
|
||||
|
||||
Cross post without links
http://www.excelguru.ca/forums/showt...-Users-to-edit https://www.msofficeforums.com/excel/...vant-user.html https://www.gittprogram.com/question...s-to-edit.html http://www.ozgrid.com/forum/showthread.php?t=200534 Please take a moment to read forum rules
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
Assuming that these are the first names of the usernames of your users, then you could employ something like the following.
Set the default state to be locked, and use password protection. Put the password in the macro where indicated and password protect the VBA project. Save the worksheet as macro enabled, and if the users don't allow the code, they won't be able to edit anything, and they will only be able to edit any line that has the first name of their username in column A. You can modify this to suit your requirements. Note that Excel is not a multi-user format. Only one person can have control of the workbook at a time. Code:
Private Sub Workbook_Open() 'In the ThisWorkbook module of the workbook Dim strName As String Dim lngLast As Long, i As Integer Const strPassword As String = "password" 'Your password lngLast = Cells(Rows.Count, "A").End(xlUp).Row strName = Trim(Split(Environ("UserName"), Chr(32))(0)) ActiveSheet.Unprotect Password:=strPassword For i = 2 To lngLast If Not Cells(i, 1) = strName Then Rows(i).Locked = True 'Rows(i).Hidden = True Else Rows(i).Locked = False 'Rows(i).Hidden = False End If Next i ActiveSheet.Protect Password:=strPassword lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#4
|
|||
|
|||
Thanks pls close this
Dear gmayor,
Thanks a lot that was exactly what i was looking for it works great. Regards, |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Check Box Content Control when checked users are presented with an option | cryder | Word | 0 | 01-07-2016 05:11 AM |
Edit table in content control | denise do rocio maciel | Word VBA | 2 | 01-03-2016 11:59 PM |
Clicking the selected Content Control checkbox returns wrong control in vba event | DougsGraphics | Word VBA | 2 | 06-24-2015 07:31 AM |
Word 2003 Document Opens In control toolbox Edit Mode | rangasamy007 | Word | 1 | 09-16-2011 04:12 AM |
Set text in edit control on inspector window indicating time Outlook 2007 | saniltalathi | Outlook | 0 | 02-20-2009 08:43 AM |