Using two VBA codes at the same time

Shayma

New Member
Joined
May 3, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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*
VBA Code:
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*
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 3, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
OK, assuming that those two Worksheet_Change do what they are supposed to, you can try combining them together into one like this:
VBA Code:
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
 
Solution

Shayma

New Member
Joined
May 3, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
OK, assuming that those two Worksheet_Change do what they are supposed to, you can try combining them together into one like this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help.

And welcome to the Board!
:)
 

Forum statistics

Threads
1,141,072
Messages
5,704,134
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top