Rob342
06-26-2009, 12:03 PM
Hi All
I have 2 pieces of code on a sheet as per example when a duplicate is entered on 2 rows it goes into the change event ok and works fine, but when i press the command button to delete certain cells it goes into the change event routine again ?
Anybody got any ideas ?
Here is the code:
Private Sub CommandButton1_Click()
'
'delete all data from fields
Range("E5").Select
ActiveCell.ClearContents
Range("J5:L5").Select
ActiveCell.ClearContents
Range("Q5:R5").Select
ActiveCell.ClearContents
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dups As Long
If Target.Areas.Count > 1 Then Exit Sub
With Me.Range("B27:B38")
Dups = Application.WorksheetFunction.CountIf(Me.Range("B27:B38"), Target(1, 1).Value)
If Dups > 1 Then
If MsgBox("RTS Code Duplicated ! Do You Want To Accept The Duplicate ?", vbYesNo) = vbYes Then
ActiveCell.Offset(1, 0).Select
End If
End If
End With
End Sub
I have 2 pieces of code on a sheet as per example when a duplicate is entered on 2 rows it goes into the change event ok and works fine, but when i press the command button to delete certain cells it goes into the change event routine again ?
Anybody got any ideas ?
Here is the code:
Private Sub CommandButton1_Click()
'
'delete all data from fields
Range("E5").Select
ActiveCell.ClearContents
Range("J5:L5").Select
ActiveCell.ClearContents
Range("Q5:R5").Select
ActiveCell.ClearContents
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dups As Long
If Target.Areas.Count > 1 Then Exit Sub
With Me.Range("B27:B38")
Dups = Application.WorksheetFunction.CountIf(Me.Range("B27:B38"), Target(1, 1).Value)
If Dups > 1 Then
If MsgBox("RTS Code Duplicated ! Do You Want To Accept The Duplicate ?", vbYesNo) = vbYes Then
ActiveCell.Offset(1, 0).Select
End If
End If
End With
End Sub