How to activate the below both codes in the same sheet

Tariq384

New Member
Joined
Jul 23, 2020
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Dear All,

Please advise me how to activate the below (1 & 2) Codes to be functioning on the same sheet and your help is highly appreciated.

1) To show the user name on specific cell based on another cell value
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or Target.Column = 2 Then
ThisRow = Target.Row
If (ThisRow = 1) Then Exit Sub
' time stamp corresponding to cell's last update
Range("D" & ThisRow).Value = Now
' Windows level UserName | Application level UserName
Range("C" & ThisRow).Value = Environ("username") & "|" & Application.UserName
Range("C:D").EntireColumn.AutoFit
End If
End Sub


2) To Lock the cell after editing
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
ActiveSheet.Unprotect Password:="01234"
For Each cel In Target
If cel.Value <> "" Then
cel.Locked = True
End If
Next cel
ActiveSheet.Protect Password:="01234"
Exit Sub

End Sub


I Like to activate them on the same worksheet.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
Please remember to use the correct tags when posting code. The easy way is to click the </> icon and post your code into the pop up window (only your code goes onto that window, not your actual post or any additional text).

See if this does what you need
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
With Target
    If .Column = 1 Or .Column = 2 Then
        If .Row > 1 Then
    ' time stamp corresponding to cell's last update
        Cells(.Row, 4).Value = Now
    ' Windows level UserName | Application level UserName
        Cells(.Row, 3).Value = Environ("username") & "|" & Application.UserName
        Range("C:D").EntireColumn.AutoFit
    End If
    ActiveSheet.Unprotect Password:="01234"
        For Each cel In .Cells
            .Locked = (.Value <> "")
        Next
    ActiveSheet.Protect Password:="01234"
End With
End Sub
 

Tariq384

New Member
Joined
Jul 23, 2020
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Thank you for your advice and quick reply , and apologize as I'm new here

I'm getting this error could you please check

1597476319876.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
Sorry, I missed an End If
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
With Target
    If .Column = 1 Or .Column = 2 Then
        If .Row > 1 Then
                ' time stamp corresponding to cell's last update
            Cells(.Row, 4).Value = Now
                ' Windows level UserName | Application level UserName
            Cells(.Row, 3).Value = Environ("username") & "|" & Application.UserName
            Range("C:D").EntireColumn.AutoFit
        End If
    End If
    ActiveSheet.Unprotect Password:="01234"
        For Each cel In .Cells
            .Locked = (.Value <> "")
        Next
    ActiveSheet.Protect Password:="01234"
End With
End Sub
 

Tariq384

New Member
Joined
Jul 23, 2020
Messages
10
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

thanks you but I don't know why still i face some errors , if you still there to help please check the below

I'm still not getting the user name and getting this error instead

Note: Once I fill the request # i need to get the now date and username then locking the filled cells

1597484285048.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
I don't see an error, at the moment the cell in column I is empty because you have stopped the code before the name has been entered.
 

Tariq384

New Member
Joined
Jul 23, 2020
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Thank you , it's working fine now , the cells were protected before entering the values,

thanks for your support.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,390
Members
412,386
Latest member
Yasaman
Top