VBA Combine

[CI Dan]

Hi all,
Thanks for looking into this in advance ;

I have two VBA codes that alone works perfectly; however whenI add them both onto one sheet 2^nd code doesn’t work. Could someoneplease rewrite this so both can work?
This VBA stamps date and time in column C when entry in columnB is made –



Private Sub Worksheet_Change(ByVal Target As Range)

IfTarget.Cells.Count > 1 Then Exit Sub
If NotIntersect(Target, Range("B2:B1000")) Is Nothing Then
With Target(1,2)
.Value = Now
End With
End If
End Sub

Where this one locks the cell in range when entry is made in columnsB to G -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTojustenditall
Application.EnableEvents = False
If NotIntersect(Target, Range("B4:G1003")) Is Nothing Then
If Target.Value<> "" Then
ActiveSheet.UnprotectPassword:="justme"
Target.Locked = True
End If
End If
ActiveSheet.ProtectPassword:="justme"
justenditall:
Application.EnableEvents = True
End Sub

 

[bobsan42]

Both procedures are triggered by the same event so they must go in one routine.

Private Sub Worksheet_Change(ByVal Target As Range)


IfTarget.Cells.Count > 1 Then Exit Sub
If NotIntersect(Target, Range("[COLOR=#ff0000]B2:B1000[/COLOR]")) Is Nothing Then
With Target(1,2)
.Value = Now
End With
End If



On Error GoTojustenditall
Application.EnableEvents = False
If NotIntersect(Target, Range("[COLOR=#ff0000]B4:G1003[/COLOR]")) Is Nothing Then
If Target.Value<> "" Then
ActiveSheet.UnprotectPassword:="justme"
Target.Locked = True
End If
End If
ActiveSheet.ProtectPassword:="justme"
justenditall:
Application.EnableEvents = True
End Sub

I am only guessing, but i believe the ranges marked in red above must be the same.

 

[Joe4]

I am only guessing, but i believe the ranges marked in red above must be the same.

No, they do not need to be the same. It is two completely different blocks of code, so they can have their own ranges.

However, there is overlap between the two ranges (B4:B1000). So any change to a value in the overlap range will actually hit BOTH blocks of code.
Is that really what you want?

Maybe if you explain exactly what you are trying to do with each block, it will help clarify things (I try not to assume what people intend to happen by what is written, because what they have written may not actually what they really intended).

 

[CI Dan]

Thanks for looking in to this;

You right there are two completely different actions; what I'm trying to achieve is: 1) stamp date in column C when entry is made in column B and; 2) when entry is made into cells in range (B4:G1000) cell to be locked for editing so the entry can not be changed. This also apply to date stamp (see no 1)) hence the overlap.

Hope this makes sense
Dan

 

[Joe4]

If column C is a date stamp of when column B is updated, shouldn't column C always be locked from editing (so no one can enter into it or change the date stamp)?

 

[CI Dan]

Can be but will this prevent "date stamp VBA" inserting?

 

[Joe4]

Can be but will this prevent "date stamp VBA" inserting?

Not if it is written correctly. Just like you are currently doing with your code, you can unprotect it in the code, add the datetime stamp, and re-protect the sheet.

So, start off locking the cells C2:C1000 and protecting the sheet.

Then, use this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim protectRng As Range
    Dim dtStampRng As Range
    
'   Set ranges to check
    Set protectRng = Range("B4:G1003")
    Set dtStampRng = Range("B2:B1000")

'   Exit if more than one cell being updated
    If Target.Cells.Count > 1 Then Exit Sub
    
'   Check to see if anything updated in protected range
    If Not Intersect(Target, protectRng) Is Nothing Then
        If Target.Value <> "" Then
            ActiveSheet.Unprotect Password:="justme"
            Target.Locked = True
            ActiveSheet.Protect Password:="justme"
        End If
    End If

'   Check to see if anything updated in column B and add date/time stamp in column C
    If Not Intersect(Target, dtStampRng) Is Nothing Then
        ActiveSheet.Unprotect Password:="justme"
        Target.Offset(0, 1) = Now()
        ActiveSheet.Protect Password:="justme"
    End If

End Sub

 

[CI Dan]

This works perfectly, thank you for support

Regards
Dan

 

[Joe4]

You are welcome.
Glad I was able to help!