How to Control Users to edit

gmayor · #1 07-24-2016, 01:28 AM

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

Damn. I hadn't seen the moderator's comment before posting

dolphine4u · #2 07-27-2016, 07:06 PM

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