Worksheet Change Event Only If Cell Is not Blank

[ASanders]

I have a worksheet change event which i would like to fire only if the cell was originally populated and then changed (i.e. if it is blank and then the user populates the cell, do not fire the worksheet change event). Heres the code I have. Every time a user changes a previously populated cell in column D I want the message box to appear, otherwise do nothing:

If Target.Column = 4 Then If Target.Offset(, 1).Value <> "" Then MsgBox "Please note products available are specific to either Personal or Corporate applications."

Any ideas?

 

[Biz]

Try code below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'copy previous value to another sheet
Sheet2.Range(Target.Address) = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ecells As Range
Dim PreviousValue As Range
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 4 And IsEmpty(Target) Then Exit Sub
If Sheet1.Range(Target.Address).Value <> "" And Sheet2.Range(Target.Address).Value <> "" And Not Sheet1.Range(Target.Address).Value = Sheet2.Range(Target.Address).Value Then
MsgBox "Please note products available are specific to either Personal or Corporate applications."
End If

'Code below adds comments so you can see the changes plus shows previous value
'//clearing more than one cell causes an error
On Error Resume Next
'//(can't overwrite an existing comment)
Target.ClearComments
With Target
'get the previous value when value changes
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
End With

End Sub

Does it give you your desired result?

Biz

 

[Biz]

Try revised code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'copy previous value to another sheet
      Sheet2.Range(Target.Address) = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And IsEmpty(Target) Then Exit Sub
   If Sheet1.Range(Target.Address).Value <> "" And Sheet2.Range(Target.Address).Value <> "" And Not Sheet1.Range(Target.Address).Value = Sheet2.Range(Target.Address).Value Then
   MsgBox "Please note products available are specific to either Personal or Corporate applications."
   End If
   
'//clearing more than one cell causes an error
      On Error Resume Next
      '//(can't overwrite an existing comment)
      Target.ClearComments
      With Target
            'get the previous value when value changes
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
      End With
   
End Sub

 

[ASanders]

It does. I should have given you the whole code though as I have a number of worksheet_change events taking place. Essentially for that column 4, if there are cells with contents already there and the user changes the content I want the code to delete the contents of AJ:AW and show the message box. If there's nothing in the cell though i dont want anything to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D:BF")) Is Nothing Then Exit Sub
If Target.Column = 13 Then Target.Offset(, 1).Resize(, 5).Value = ""
If Target.Column = 13 Then MsgBox "You have altered the members country of residence. Please re-enter state, post code and telephone number fields before generating data file."
If Target.Column = 4 Then Target.Offset(, 32).Resize(, 23).Value = ""
If Target.Column = 4 Then If Target.Offset(, 1).Value <> "" Then MsgBox "Please note products available are specific to either Personal or Corporate applications."
If Target.Column = 42 Then Target.Offset(, 4).Resize(, 1).Value = ""
If Target.Column = 42 Then MsgBox "If you are adding TPD in addition to Death, please ensure the TPD and Death product are identical before generating data file."

End Sub