I have a macro that takes data (name, month, amount etc..) from one worksheet
and enters it into a table on another sheet. What I want is a message box to
ask the user if he/she is sure the data needs to be entered if the name to be
entered in the name column of the table would create a duplicate in that
column, as a duplicate entry would be rare. The more likely senario would be
that the user entered the wrong month in the first sheet and needs to correct
it.
Any help is greatly appreciated.

Something like the below...Change the <entername> to a name string or cell
reference. ws1 in the below example is Sheet2. Adjust to suit. The below code
checks whether the name already exists in Sheet2 Column A. If exists the
procedure exists. or otherwise will continue

Sub Macro
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Sheet2")

If WorksheetFunction.CountIf(ws1.Range("A:A"), "<entername>") > 0 Then
If MsgBox("This name already exists. Are you sure to copy ?", vbYesNo + _
vbQuestion + vbDefaultButton2) <> vbYes Then Exit Sub
End If

'Your code to copy continue here

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"The Inspector" wrote:

> I have a macro that takes data (name, month, amount etc..) from one worksheet
> and enters it into a table on another sheet. What I want is a message box to
> ask the user if he/she is sure the data needs to be entered if the name to be
> entered in the name column of the table would create a duplicate in that
> column, as a duplicate entry would be rare. The more likely senario would be
> that the user entered the wrong month in the first sheet and needs to correct
> it.
> Any help is greatly appreciated.

Hello,

A worksheet function countif approach is suboptimal if you need to
check a large amount of data. Please see an analysis of an analogous
case:
http://www.sulprobil.com/html/count_unique.html

I would suggest some code fragment similar to my UDFs Pstat or Pfreq:
http://www.sulprobil.com/html/pstat.html
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd

Thanks.
If WorksheetFunction.CountIf(ws1.Range("A:A"), "<entername>") > 0
"Jacob Skaria" wrote:

> Something like the below...Change the <entername> to a name string or cell
> reference. ws1 in the below example is Sheet2. Adjust to suit. The below code
> checks whether the name already exists in Sheet2 Column A. If exists the
> procedure exists. or otherwise will continue
>
> Sub Macro
> Dim ws1 As Worksheet
> Set ws1 = ActiveWorkbook.Sheets("Sheet2")
>
> If WorksheetFunction.CountIf(ws1.Range("A:A"), "<entername>") > 0 Then
> If MsgBox("This name already exists. Are you sure to copy ?", vbYesNo + _
> vbQuestion + vbDefaultButton2) <> vbYes Then Exit Sub
> End If
>
> 'Your code to copy continue here
>
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "The Inspector" wrote:
>
> > I have a macro that takes data (name, month, amount etc..) from one worksheet
> > and enters it into a table on another sheet. What I want is a message box to
> > ask the user if he/she is sure the data needs to be entered if the name to be
> > entered in the name column of the table would create a duplicate in that
> > column, as a duplicate entry would be rare. The more likely senario would be
> > that the user entered the wrong month in the first sheet and needs to correct
> > it.
> > Any help is greatly appreciated.