Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
Please don't post the same message to lots of newsgroups.
Zigball wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
Who are you Dave Peterson the owner of google or JigSaw?
Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
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 wrote: Who are you Dave Peterson the owner of google or JigSaw? Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
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 wrote: 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 wrote: Who are you Dave Peterson the owner of google or JigSaw? Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
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 Zigball wrote: 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 wrote: 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 wrote: Who are you Dave Peterson the owner of google or JigSaw? Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
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 On 14 Oct 2006 14:57:06 -0700, "Zigball" wrote: 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 wrote: 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 wrote: Who are you Dave Peterson the owner of google or JigSaw? Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
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 wrote: 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 On 14 Oct 2006 14:57:06 -0700, "Zigball" wrote: 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 wrote: 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 wrote: Who are you Dave Peterson the owner of google or JigSaw? Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Prevent Duplicate Data from inputing using input application?
Cross-post is when you post one message with several news groups in the
"newsgroups" dialog. microsoft.public.worksheet.functions,microsoft..pu blic.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 On 16 Oct 2006 10:10:46 -0700, "Zigball" wrote: 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 wrote: 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 On 14 Oct 2006 14:57:06 -0700, "Zigball" wrote: 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 wrote: 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 wrote: Who are you Dave Peterson the owner of google or JigSaw? Thanks for your help! Dave Peterson wrote: Please don't post the same message to lots of newsgroups. Zigball wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Prevent Duplicate Data from inputing using input application? | New Users to Excel | |||
Data Valitaion Input message | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |