Using two VBA codes at the same time

[Shayma]

hello

I am trying to use the below VBA codes, but it seems only one code can be used and dont know how to use multiple VBA codes for the same sheet. The first one is to lock the cell after a value is entered and the second one is writing the date and time when value was entered.


*First*

Dim mRg As Range
Dim mStr As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("B:B"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
    mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("B:B"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="Boutiqaat123"
    If xRg.Value <> mStr Then xRg.Locked = True
    Target.Worksheet.Protect Password:="Boutiqaat123"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("B:B"), Target) Is Nothing Then
    Set mRg = Target.Item(1)
     mStr = mRg.Value
End If
End Sub

*Second*

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        With Target(1, 2)
        .Value = Date & " " & Time
        .EntireColumn.AutoFit
        End With
    End If
End Sub

 

[Joe4]

Those are two entirely different event procedures, meaning they fire at different times.

The first one is actually triggered on a "double-click", and the other is triggered when a cell value is manually changed.

If something is not working for you, please lay out the exact scenario. Tell us exactly what you are doing on the sheet, and what it supposed to happen.

 

[Shayma]

Those are two entirely different event procedures, meaning they fire at different times.

The first one is actually triggered on a "double-click", and the other is triggered when a cell value is manually changed.

If something is not working for you, please lay out the exact scenario. Tell us exactly what you are doing on the sheet, and what it supposed to happen.

I have a couple of cells which will be entered by the employees, I want once these cells are filled, they will be locked and no modification can be done and at the same time there will be an adjacent column showing the date and time that the employees filled each cell.

 

[Joe4]

OK, assuming that those two Worksheet_Change do what they are supposed to, you can try combining them together into one like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim xRg As Range
    
    On Error Resume Next
    
    Set xRg = Intersect(Range("B:B"), Target)
    
    If xRg Is Nothing Then Exit Sub
    
    Target.Worksheet.Unprotect Password:="Boutiqaat123"
    
    Application.EnableEvents = False
    With Target(1, 2)
        .Value = Date & " " & Time
        .EntireColumn.AutoFit
    End With
    Application.EnableEvents = True
    
    If xRg.Value <> mStr Then xRg.Locked = True
    
    Target.Worksheet.Protect Password:="Boutiqaat123"
    
End Sub

 

[Shayma]

OK, assuming that those two Worksheet_Change do what they are supposed to, you can try combining them together into one like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim xRg As Range
   
    On Error Resume Next
   
    Set xRg = Intersect(Range("B:B"), Target)
   
    If xRg Is Nothing Then Exit Sub
   
    Target.Worksheet.Unprotect Password:="Boutiqaat123"
   
    Application.EnableEvents = False
    With Target(1, 2)
        .Value = Date & " " & Time
        .EntireColumn.AutoFit
    End With
    Application.EnableEvents = True
   
    If xRg.Value <> mStr Then xRg.Locked = True
   
    Target.Worksheet.Protect Password:="Boutiqaat123"
   
End Sub

Thank you sooo much it worked !

 

[Joe4]

You are welcome.
Glad I was able to help.

And welcome to the Board!