Calculate event to do what Change event does.
 

The changes in column C are the result of formulas.

The Change Event works fine for changes entered in column C.

Not getting the Calculate to either 'see' the change/calculate or Evaluate the Countif when the formulas produce a duplicate in column C.

Thanks.

Howard


Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = Columns(3)

If Evaluate("Countif(C1:C100," & Target.Address & ")") 1 Then
MsgBox Target.Value & " is a duplicate entry. It will be removed.", vbExclamation, "Data Entry Editor"
Target.Resize(1, 10).ClearContents
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count 1 Then Exit Sub
If Target.Column = 3 And Len(Target.Value) 0 Then

If Evaluate("Countif(C:C," & Target.Address & ")") 1 Then
MsgBox Target.Value & " is a duplicate entry. It will be removed.", vbExclamation, "Data Entry Editor"
Target.Resize(1, 10).ClearContents
End If

End If

End Sub

Calculate event to do what Change event does.
 

Hi Howard,

Am Sun, 2 Aug 2015 04:55:33 -0700 (PDT) schrieb L. Howard:

The changes in column C are the result of formulas.

The Change Event works fine for changes entered in column C.

Not getting the Calculate to either 'see' the change/calculate or Evaluate the Countif when the formulas produce a duplicate in column C.

please post the formula in from column C


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Calculate event to do what Change event does.
 

please post the formula in from column C


Regards
Claus B.

Hi Claus,

I don't know the actual formulas used in C, I was using =A1 in a cell and in another cell =A2. Then I was giving A1 and A2 the same value to produce a duplicate value in C.

Would that be sufficient to fire a calculate event if the code was written correctly?

Howard

Calculate event to do what Change event does.
 

Hi Howard,

Am Sun, 2 Aug 2015 06:11:10 -0700 (PDT) schrieb L. Howard:

I don't know the actual formulas used in C, I was using =A1 in a cell and in another cell =A2. Then I was giving A1 and A2 the same value to produce a duplicate value in C.

Would that be sufficient to fire a calculate event if the code was written correctly?

if you know which cell fires the calculation you can check this cell
with Worksheet_Change event.
Perhaps I can change the formula to the expected output if I know how it
looks like.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Calculate event to do what Change event does.
 

Hi Howard,

Am Sun, 2 Aug 2015 06:11:10 -0700 (PDT) schrieb L. Howard:

I don't know the actual formulas used in C, I was using =A1 in a cell and in another cell =A2. Then I was giving A1 and A2 the same value to produce a duplicate value in C.

Would that be sufficient to fire a calculate event if the code was written correctly?

if a value in column A fires the calculation you could create a data
validation for column A = Custom = =COUNTIF(A:A,A1)=1
Then no duplicates can be entered in A.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Calculate event to do what Change event does.
 

if a value in column A fires the calculation you could create a data
validation for column A = Custom = =COUNTIF(A:A,A1)=1
Then no duplicates can be entered in A.


Regards
Claus B.

Hi Claus,

I can see I don't have enough information about the sheet and column in question.

I'll inquire some more. If I get more info, then I'll post back.

Thanks.

Howard