MS Excel: How to Prevent Duplicate Data from inputing using input application?

[Zigball]

Hello microsoft.public.office.developer.com.add_ins 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 @ (e-mail address removed)


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

 

[XL-Dennis]

Hi Zig,

The following procedure is created in Excel but it shouldn't be hard to
transform it to Your COM add-in.

Sub Check_if_Value_Exist()
Dim vaValue As Variant

'Get the number from the end user.
vaValue = Application.InputBox(Prompt:="Please add a number", _
Title:="My Application", Type:=1)

'If the user cancel the operation.
If vaValue = False Then Exit Sub

'Check if the number exist or not.
With Application
If .WorksheetFunction.CountIf(.Range("C2:C10"), vaValue) > 1 Then
MsgBox "That number already exist."
'more code...
Else
'other action
End If
End With

End Sub

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Zigball]

This info was great Dennis thanks alot, do you know a way that I can
prevent the data from being entered after I close the message box?

 

[XL-Dennis]

Hi Zig,

One way is to protect the worksheet Your solution target and when an unique
value is entered then unprotect, write the value and protect the worksheet as
the following procedure shows:

Option Explicit

Sub Check_if_Value_Exist()
Dim wbBook As Workbook
Dim wsTarget As Worksheet
Dim vaValue As Variant

'Get the number from the end user.
vaValue = Application.InputBox(Prompt:="Please add a number", _
Title:="My Application", Type:=1)

'If the user cancel the operation.
If vaValue = False Then Exit Sub

Set wbBook = ActiveWorkbook
Set wsTarget = wbBook.Worksheets(1)


'Check if the number exist or not.
With Application
If .WorksheetFunction.CountIf(.Range("C2:C10"), vaValue) > 1 Then
MsgBox "That number already exist."
'more code...
Else
With wsTarget
'Unprotect the worksheet
.Unprotect Password:="Secret"
'Write the value
.Range("C10").Value = vaValue
'Protect the worksheet
.Protect Password:="Secret"
End With
End If
End With

End Sub

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Zigball]

Hello Dennis,

I really do appreciate your help and good help it is. I am having
trouble getting the last part of it to work the count IF function. I
was able to enter the code into my application and indeed it counted
the duplicate data. The problem is that as I said early i need to
prevent the duplicate data from entering the spreadsheet completely.
You gave me the code to do this but I can not figure it out could you
please help me. I was wondering wheter or not it was something as
simple as protecting the spreadsheet manually. I assumed that I would
not have to do any manual protecting and assumed the code that you gave
me would do the job in code automatic, unsure? Could you show me how
the code should go directly into this project if possile, ThankYou for
your help!

 

[XL-Dennis]

Hi again,

I apologize for coming back so late to You but I didn't received any
notification via e-mail about Your last e-mail.

No, You should not do any manually protecting of the worksheet *but* You
need to do it the first time sp that the worksheet is initially protected.
Then You can use the code as showed in my previously post.

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[Zigball]

Thanks xlDennis it works I'll talk to you later!