How to Prevent Duplicate Data from inputing using input application?

[Zigball]

Hello microsoft.public.excel.worksheet.functions my name is Zig I am
trying hard to find out how I can use a input application to input data
into a specified excel sheet. I have learned how to input the data into
the excel sheet although I need to prevent the inputs from being
duplicated. I have used a validating solver to prevent duplicate
entries but it only works if you type the text into the sheet. I am
unable to get the input application to follow the validation rule. Is
there a way that I can use a input application to prevent duplicate
entries into the excel sheets and if duplicate data is true can i
redirect it to another specified sheet? Please help! If you can help
please email me @ Zigball @ Gmail.com


This is a code that I use to get a input into the spreadsheet.

' frmAddresses class
Option Explicit
Private Sub UserForm_Initialize()

'Load the combobox with states.
cmbStates.AddItem "AL"
cmbStates.AddItem "AR"
cmbStates.AddItem "AZ"
cmbStates.AddItem "CA"
cmbStates.AddItem "CO"
cmbStates.AddItem "MD"
cmbStates.AddItem "NC"
cmbStates.AddItem "NY"
cmbStates.AddItem "WV"

End Sub


Private Sub txtZip_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

' Pass through only digits.
If KeyCode < 48 Or KeyCode > 57 Then
KeyCode = 0
Beep
End If

End Sub

Public Function ValidateData() As Boolean

' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.

If txtFirstName.Value = "" Then
MsgBox "You must enter a first name."
ValidateData = False
Exit Function
End If
If txtLastName.Value = "" Then
MsgBox "You must enter a last name."
ValidateData = False
Exit Function
End If
If txtAddress.Value = "" Then
MsgBox "You must enter an address."
ValidateData = False
Exit Function
End If
If txtCity.Value = "" Then
MsgBox "You must enter a city."
ValidateData = False
Exit Function
End If
If cmbStates.Value = "" Then
MsgBox "You must select a state."
ValidateData = False
Exit Function
End If
If txtZip.TextLength <> 5 Then
MsgBox "You must enter a 5 digit zip code."
ValidateData = False
Exit Function
End If

ValidateData = True

End Function


Public Sub ClearForm()

'Clears all data from the form.
txtFirstName.Value = ""
txtLastName.Value = ""
txtAddress.Value = ""
txtCity.Value = ""
txtZip.Value = ""
cmbStates.Value = ""

End Sub

Public Sub EnterDataInWorksheet()

'Copies data from the user form
'to the next blank row in the worksheet.

Dim r As Range, r1 As Range

Set r = Worksheets("Addresses").Range("A2").CurrentRegion
Set r1 = r.Offset(r.Rows.Count, 0)
r1.Cells(1).Value = txtFirstName.Value
r1.Cells(2).Value = txtLastName.Value
r1.Cells(3).Value = txtAddress.Value
r1.Cells(4).Value = txtCity.Value
r1.Cells(5).Value = cmbStates.Value
r1.Cells(6).Value = txtZip.Value

End Sub

Private Sub cmdCancel_Click()

ClearForm
Me.Hide

End Sub

Private Sub cmdDone_Click()

If ValidateData = True Then
EnterDataInWorksheet
ClearForm
Me.Hide
End If

End Sub

Private Sub cmdNext_Click()

If ValidateData = True Then
EnterDataInWorksheet
ClearForm
End If

End Sub

 

[Dave Peterson]

Please don't post the same message to lots of newsgroups.

 

[Zigball]

Who are you Dave Peterson the owner of google or JigSaw?
Thanks for your help!

 

[Dave Peterson]

I'm one of the people who see the same post in multiple newsgroups and realize
that potential responders may be wasting their time if you already have an
answer at one of your other posts.

My response was a request to you and a warning to others that any response could
be a waste of their time. And heck, do you really want to have to read all the
followups to all the threads you started? Isn't that a waste of your time?

You may not appreciate this, but when I see others warning me that the same
message is multiposted to several newsgroups, I appreciate the warning.

 

[Zigball]

Ironically Dave from all the post that I've created I have only one
response. I am new to Google groups which is probably one of the best
websites I've ever been to, keep up the good work Dave. I was hoping
for and answer from any responders that's why I gave a sarcastic
remark. I am still looking for an answer can you help me or are you
just a administrator of this site officially or unofficially either way
I understand what you are saying and I will try to keep my post to a
minimum. Although my topics could apply to different groups so that's
why I will selectively find the right groups for my problems.
Thanks Dave

 

[Dave Peterson]

I'm not any type of administrator.

I'm not sure how you define the duplicate. If you're going to use a single
field on the form (say LastName), you could look for it in the column in the
worksheet.

if application.countif(Worksheets("Addresses").range("b:b"), _
me.txtLastName.Value) > 0 then
'already exists, issue warning?
else
'add the record
end if

Ironically Dave from all the post that I've created I have only one
response. I am new to Google groups which is probably one of the best
websites I've ever been to, keep up the good work Dave. I was hoping
for and answer from any responders that's why I gave a sarcastic
remark. I am still looking for an answer can you help me or are you
just a administrator of this site officially or unofficially either way
I understand what you are saying and I will try to keep my post to a
minimum. Although my topics could apply to different groups so that's
why I will selectively find the right groups for my problems.
Thanks Dave

 

[Gord Dibben]

Then why don't you cross-post instead of multi-posting?

Then those of us with real news readers will be able to mark which are
cross-posted.

If you continue to multi-post you may find you receive very little help from
those who have a "plonk" button.


Gord Dibben MS Excel MVP

Ironically Dave from all the post that I've created I have only one
response. I am new to Google groups which is probably one of the best
websites I've ever been to, keep up the good work Dave. I was hoping
for and answer from any responders that's why I gave a sarcastic
remark. I am still looking for an answer can you help me or are you
just a administrator of this site officially or unofficially either way
I understand what you are saying and I will try to keep my post to a
minimum. Although my topics could apply to different groups so that's
why I will selectively find the right groups for my problems.
Thanks Dave

 

[Zigball]

Cross-Post? If you don't know I am new to google groups.
I see that you would be able to help me out.

 

[Gord Dibben]

Cross-post is when you post one message with several news groups in the
"newsgroups" dialog.

microsoft.public.worksheet.functions,microsoft..public.excel,microsoft.public.excel.misc

Note the comma between each group.

All the regulars monitor all the Excel news groups, so if the topic is Excel, no
need to post to more than one group.

Although I should amend that..........the programming group is strictly for VBA
so if you have a VBA question you would be best posting to that group.


Gord