Conflict between two codes in the same project

[anvs]

Hi!

In Col A process references are registered.
In Col B the code registers the DateTime of the entry of each process.
In Col C the code records the DateTime of any change in the reference of each process.
For that I use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastDataEntry

If Target.Column > 1 Then Exit Sub
Application.EnableEvents = False
LastDataEntry = Target: Application.Undo

On Error Resume Next
If LastDataEntry <> Target Then
Target = LastDataEntry
If Target(, 2) = "" Then
Target(, 2) = Now
else
Target(, 3) = Now
End If
End If

In prototype it works fine.

I recently realized the need to make changes to the first 20 records (A2:A20) unfeasible.
Please, from rollis 13 I have the following code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A20")) Is Nothing Then
Application.OnKey "{DELETE}", ""
Else
Application.OnKey "{DELETE}"
End If
End Sub

In prototype it works fine.

When I try to use both codes in the project, the second one doesn't work.
I ask for your help because I cannot understand the conflict.
Thanks in advance.
anvs

 

[offthelip]

Did you ever try the code I suggested to prevent deletion of data in specific range see post #2 in Protect columns and cells via VBA , It was different to rollis13 code because it prevented SELECTION of the those cells which in turn prevents deletion of the cells. Your reply to my comment seemed to have confused my code with rollis13 code . It would be much easier to combine with other code because it doesn't use the onkey and it isn't in the worksheet change event code so shouldn't effect it at all
Try my code on a test worksheet on its own the code was

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' this prevent any editing or deletion in columns B and C even if you try selecting the entire row (it doesn't let you)
 If Not (Intersect(Target, Range("B:B")) Is Nothing) Then
 Cells(Target.Row, 1).Select
End If
If Not (Intersect(Target, Range("C:C")) Is Nothing) Then
 Cells(Target.Row, 4).Select
End If

End Sub

 

[anvs]

Thank you for the answer.
Yes, I tried your code of course.
Sorry but I thought I answered you but I'll try on a clean prototype.
Only later will I be able to respond with the result.
Thanks again .
anvs

 

[offthelip]

If you succeed in deleting anything in columns B and C with my code running let me know how you did it, because I don't think it is possible

 

[offthelip]

I have slightly improved the code because this code allows you to select rows or multiple rows and columns and it just modifies the selelction to exclude columns B and C ( or any other range you want to specify)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' this prevent any editing or deletion in columns B and C even if you seelct the entire row
Dim c As Range
minrow = 1048576
maxrow = 1

maxcol = 4

For Each c In Target
 If c.Row > maxrow Then maxrow = c.Row
 If c.Row < minrow Then minrow = c.Row
 If c.Column > maxcol Then maxcol = c.Column
 
Next c
If Not (Intersect(Target, Range("B:B")) Is Nothing) Then
 Range(Cells(minrow, 1), Cells(maxrow, 1)).Select
End If
If Not (Intersect(Target, Range("C:C")) Is Nothing) Then
 Range(Cells(minrow, 4), Cells(maxrow, maxcol)).Select
End If
End Sub

 

[anvs]

As posted in #1, I have a working code that works fine.

Checking your code on a test prototype, it works for the feat you propose. However, in conjunction with the existing project, it does not work, as I tried to explain earlier.

I would be immensely grateful if you could have code that would work with the existing one and protect cells A2:A20.

anvs

 

[offthelip]

this will prevent selection of the RAnge A2 to A20

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' this prevent any editing or deletion in columns B and C even if you seelct the entire row
Dim c As Range
minrow = 20
maxrow = 1
maxcol = 2
For Each c In Target
 If c.Row > maxrow And c.Row < 21 Then maxrow = c.Row
 If c.Row < minrow And c.Row > 2 Then minrow = c.Row
 If c.Column > maxcol Then maxcol = c.Column
 
Next c
If Not (Intersect(Target, Range("A2:A20")) Is Nothing) Then
 Range(Cells(minrow, 2), Cells(maxrow, maxcol)).Select
End If
End Sub

I tested your worksheet change code with this and it seems to work

 

[offthelip]

don't forget to add:

Application.EnableEvents = TRUE

at the end of your worksheet change code otherwise nothing is going to work

 

[anvs]

In an isolated prototype your code works great.
In conjunction with what is running, it has no effect.

Trying to clarify: I'm trying to avoid changing the first 19 records (between A2:A20)

 

[offthelip]

I don't know what you have got running but in my test worksheet this is what i have got, and it prevent changes in range A2 A20 ,
if I change any cell from A20 onward in column A it puts the date in column B and then the second change in column C, I can make changes in columns B onwards on any row.
This is the only code I have got running on this worksheet
NOTE I have put the application.enableevents= TRUE statement in make sure you have got that because this could be your reason for "No effect"!!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' this prevent any editing or deletion in columns B and C even if you seelct the entire row
Dim c As Range
minrow = 20
maxrow = 1
maxcol = 2
For Each c In Target
 If c.Row > maxrow And c.Row < 21 Then maxrow = c.Row
 If c.Row < minrow And c.Row > 2 Then minrow = c.Row
 If c.Column > maxcol Then maxcol = c.Column
 
Next c
If Not (Intersect(Target, Range("A2:A20")) Is Nothing) Then
 Range(Cells(minrow, 2), Cells(maxrow, maxcol)).Select
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastDataEntry

If Target.Column > 1 Then Exit Sub
Application.EnableEvents = False
LastDataEntry = Target: Application.Undo

On Error Resume Next
If LastDataEntry <> Target Then
Target = LastDataEntry
If Target(, 2) = "" Then
Target(, 2) = Now
Else
Target(, 3) = Now
End If
End If
Application.EnableEvents = True   ' YOU MUST HAVE THIS LINE IN YOUR CODE!!!!!!!
End Sub