Combining Two Worksheet VBA Codes

[Kabasa007]

Hi all,

I would like to combine two worksheet VBA codes.

The first code creates a timestamp and username of the last person to update any cell in the table, while the second code lock cells in the worksheet after data is entered into them.

See code one below

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A2:R1000000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("U" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("V" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

myUpdatedRange.Offset(, 1).Value = Application.UserName

'Turn events back on
Application.EnableEvents = True
End Sub

See Code Two Below

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet2.Unprotect "123"

If VBA.IsEmpty(Target) Then
    Target.Locked = False
    Else
    Target.Locked = True
End If

Sheet2.Protect "123"

End Sub

I want the two codes to run as one, Thanks as always.

 

[davesexcel]

Is Sheet2 the sheet with the code?

 

[Kabasa007]

Is Sheet2 the sheet with the code?

Yes, Sheet2 is the sheet with the codes.

 

[szakharov7723]

Just to clarify do you not consider moving them to modules in order to operate with them easier ?
I am not sure if you can call subs in your case,
But usually if it is module sub you can easily combine them in 1 by creating 3rd sub and calling 2 of them.

 

[Kabasa007]

Just to clarify do you not consider moving them to modules in order to operate with them easier ?
I am not sure if you can call subs in your case,
But usually if it is module sub you can easily combine them in 1 by creating 3rd sub and calling 2 of them.

I have very little understanding of how that would work. But here is the thing, the codes run automatically after a change or event occurs in the worksheet. I have other worksheets in the workbook and i would like for those code to only run in the worksheet I want them to run, that's why i am using a worksheet code. But if moving the codes to modules will solve this for me, please how do i go about that?

 

[szakharov7723]

I have very little understanding of how that would work. But here is the thing, the codes run automatically after a change or event occurs in the worksheet. I have other worksheets in the workbook and i would like for those code to only run in the worksheet I want them to run, that's why i am using a worksheet code. But if moving the codes to modules will solve this for me, please how do i go about that?

Try this first and let me know if this works.
Press alt+F11
Go to Insert
Insert Module
Paste this code

 Sub macro1()

Worksheets("your_sheet").Activate
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A2:R1000000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("U" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("V" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

myUpdatedRange.Offset(, 1).Value = Application.UserName

'Turn events back on
Application.EnableEvents = True
End Sub

Do the same with your 2nd code and then
Insert new module
Add the following code

Sub combo()
call macro1
call macro2
End sub

Run this, and it should call boths subs.

 

[Kabasa007]

Try this first and let me know if this works.
Press alt+F11
Go to Insert
Insert Module
Paste this code

 Sub macro1()

Worksheets("your_sheet").Activate
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("A2:R1000000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("U" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("V" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

myUpdatedRange.Offset(, 1).Value = Application.UserName

'Turn events back on
Application.EnableEvents = True
End Sub

Do the same with your 2nd code and then
Insert new module
Add the following code

Sub combo()
call macro1
call macro2
End sub

Run this, and it should call boths subs.

It did not work.