Hello microsoft.public.excel.charting 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!


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

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



wrote:

Hello microsoft.public.excel.charting 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!

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