hobbiton73
11-15-2012, 10:00 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below to track changes and insert pre-defined text value upon a cell change within a given range.
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range, res As Variant
Dim cell As Range
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Set rInt = Intersect(Target, Range("B7:Q350"))
If Not rInt Is Nothing Then
For Each cell In rInt
If Not IsEmpty(cell.Value2) Then
cell.Interior.ColorIndex = 35
Application.EnableEvents = False
With Rows(cell.Row)
.Range("A1").Value = Date
.Range("G1").Value = "R&D"
.Range("AE1").Value = "No"
End With
Application.EnableEvents = True
End If
Next cell
End If
End Sub
This piece of the code works fine in it's own right. But I have an issue when the user deletes a row.
Unfortunately, when the row is deleted, the 'Track change' code is then activated, when in reality there has been no cell value change.
Because I have 'locked' cells within a 'Protected' worksheet I'm using the following code to allow the user to delete the row:
Sub DelRow()
Dim msg
msg = MsgBox("Are you sure?", vbYesNo)
If msg = vbNo Then Exit Sub
ActiveSheet.Unprotect "handsoff"
Selection.EntireRow.Delete
ActiveSheet.Protect "handsoff"
End Sub
I must admit, I'm not really sure how to overcome this. I just wondered whether someone perhaps offer some guidance please on how I can stop the 'Track Change' activating when a row is deleted.
Any help would be very much appreciated.
Many thanks and klind regards
I'm using the code below to track changes and insert pre-defined text value upon a cell change within a given range.
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range, res As Variant
Dim cell As Range
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Set rInt = Intersect(Target, Range("B7:Q350"))
If Not rInt Is Nothing Then
For Each cell In rInt
If Not IsEmpty(cell.Value2) Then
cell.Interior.ColorIndex = 35
Application.EnableEvents = False
With Rows(cell.Row)
.Range("A1").Value = Date
.Range("G1").Value = "R&D"
.Range("AE1").Value = "No"
End With
Application.EnableEvents = True
End If
Next cell
End If
End Sub
This piece of the code works fine in it's own right. But I have an issue when the user deletes a row.
Unfortunately, when the row is deleted, the 'Track change' code is then activated, when in reality there has been no cell value change.
Because I have 'locked' cells within a 'Protected' worksheet I'm using the following code to allow the user to delete the row:
Sub DelRow()
Dim msg
msg = MsgBox("Are you sure?", vbYesNo)
If msg = vbNo Then Exit Sub
ActiveSheet.Unprotect "handsoff"
Selection.EntireRow.Delete
ActiveSheet.Protect "handsoff"
End Sub
I must admit, I'm not really sure how to overcome this. I just wondered whether someone perhaps offer some guidance please on how I can stop the 'Track Change' activating when a row is deleted.
Any help would be very much appreciated.
Many thanks and klind regards