Preventing Duplicate Data Entries /Please email me @ Zigball @ Gmail.com VBA Problems!

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

0
zigball (6)
10/10/2006 3:18:31 PM
excel 39879 articles. 2 followers. Follow

1 Replies
509 Views

Similar Articles

[PageSpeed] 19

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

Zigball wrote:
> 
> Hello microsoft.public.excel 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
0
petersod (12005)
10/10/2006 4:31:19 PM
Reply:

Similar Artilces:

invalid email address
In message to Candy FH Muffman wrote of having an invalid email address. I use hotmail but would like to prevent ti being used by spammers. Is there any way I can hide it or restrict it in some way? Can I make it invalid? Thank you You mean when posting to an online service like this? Sure, don't type your (correct) email address. See my address or from many others to see an example. Note: I've removed your valid address in my reply. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add...

Merging Two Office Data Files
This is a multi-part message in MIME format. ------=_NextPart_000_0038_01C3C6E7.505DB790 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Here is the situation: I've a desktop and a laptop. Before I bought the laptop I was using MS = Outlook on my desktop. Now that I'm using my laptop mostly, I would like = to have my old emails from my desktop Outlook merged with the laptop = Outlook emails. I don't want to replace the laptop outlook files with = the desktop outlook files is the folder - C:\Documents and = Settings\Laptop\Lo...

Question -Problem
Has anybody ever seen an error message (0x800cccF6) message? I cannot configure my Outlook to retrieve Hotmail. Works with other profiles on this computer running winxp. Stan-The-Man-2006 wrote: > Has anybody ever seen an error message (0x800cccF6) message? I cannot > configure my Outlook to retrieve Hotmail. Works with other profiles > on this computer running winxp. If you have a free Hotmail account, note that it won't work anymore (even your other profiles will eventually have problems) - see http://www.slipstick.com/addins/services/online.htm#hotmail ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

AD Sync problem
Hello, One of my DC was disconnected from the network for quite a long time and cannot be synchronized with AD anymore. I have been trying to apply the http://support.microsoft.com/kb/325850/en-us procedure to reset the machine account password. After launching the command "netdom resetpwd /S: ....." I've got the following error message : "The machine account password for the local machine could not be reset. Echec d'ouverture de session : unknown user or incorrect password. The command failed to complete successfully." ANy idea to help me ? Man...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Sorting emails by domains, from org to edu (right char is the most significant)
Hello All I need to sort the domains according their emails. For example: Before sorting: john1@abc.edu john3@abc.org john5@abc.com john4@bcd.org john2@bcd.edu john6@bcd.com After sorting: john3@abc.org john4@bcd.org john5@abc.com john6@bcd.com john1@abc.edu john2@bcd.edu That is, how to sort, according to the domain name ( the right is the most significant )? Thanks. Z. D. On Feb 15, 11:09 pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote: > That is, how to sort, according to the domain name ( the right is the > most significant )? you'll probably need ...

unsolicited entry in the folder "Temporary Internet Files"
Hello, I am working on a programme which browses web sites and runs under XP. The http download is as follows: pServer = Isession -> GetHttpConnection(strServerName, nPort); pFile = pServer->OpenRequest(CHttpConnection::HTTP_VERB_GET, strObject, NULL, 1, NULL, NULL, dwHttpRequestFlags); pFile->SendRequest(); pFile->QueryInfoStatusCode(dwStatusCode); if(dwStatusCode == 200) { pFile -> QueryInfo(HTTP_QUERY_LAST_MODIFIED, &sysT); status.lastMod = sysT; if(DBlastMod == status.lastMod) //URL content has not changed since the last visit ...

Excel button problem
Hi All I have a macro that copies a worksheet in the active workbook and puts it into a new workbook - then formats it and deletes any buttons on the worksheet. On the first click on the button the macro works ok. On the second click, it fails because the all assigned macros on all buttons in the active workbook changed from "mba" to "book1!mba". Book 1 doesn't exists (wasn't opened, wasn't saved, doesn't have the macros). I've never experienced this problem before?? Can anyone help to solve this problem? FYI The macro to do this is c...

Some Emails Cannot be Delivered
I have a problem that I cannot put a handle on with my exchange server. Some outbound messages do not reach their destination. The problem happens with certain destinations. However the confusing part is that some messages are able to make it through. This would mean that there are no problems with DNS or MX lookup or any connectivity issue since some emails manage to make it through. I'm at a loss and can't figure where to look first I hope that someone might have an idea. You'll have to provide more information. Are you receiving NDrs if so can you share them? Is ther...

Attaching Contacts to new email
Creating a new email. When contacts folder has "shared" contacts and "personal" contacts how can you set your personal contacts as the default? Example: creating a new email having never addressed the "send to" contact before, you hit the "To" button. Currently my "shared" contacts opens up but I would like my "personal" contacts page to open instead of having to drop down to "contacts" to bring up that list. Is there a solution to this? Thank you!!! On 2/26/2010 10:21 AM, assistantneedshelp wrote: >...

Problems with reallocations in the Advanced Budget
I am a using the Advanced Budget in Money and for years I have reallocated money between categories and months. recently when I updated my transactions all my real locations were wrong. Basically the money I allocated from March to February was not only back in February it reversed it and made it look like I sent Money from February to March so now any category that I have reallocated money (an that is most) is wrong. Does anyone know of a way to fix this, I have run the repair, but that did nothing. Microsoft Money Plus Premium Version 17.0.125.1415 I am so glad to see that I am...

Recieving email with nothing in them? Blank??
From time to time, I will recieve email from friends, family, or business...and when I get the email, I open it up to find nothing but white space. I can't find a common thing between emails. I just know it is very frustrating when I have to ask people to resend the email to me or to a new address. When I look at my mail on the server, it is fine. It is when I open Outlook and it downloads it to my desktop. I get nothing. Any ideas? Cheers.. vg Sorry, wanted to add that I am using OL2003 with WINXP and everything is updated SP wise. I am starting to see some articles on this...

WCF Client serialization problem
I posted the problem on another forum, and to prevent duplicate posts, but get as many professionals as possible to look at it, I include the url in this post. Please help! http://stackoverflow.com/questions/2948657/migrating-webclient-to-wcf-wcf-client-serializes-parametername-of-method ...

Autonumber created.. problems in the future?
I managed to create an autonumber in Microsoft CRM. I did this by making a field "Number"(in the database "New_Number") and I published it on the form. Then I went To the SQL server and I changed the field in the table to Identity Yes, Identity seed 1, Identity Increment 1. I locked the field on the form. It worked! I think that this is not supported by Microsoft. But has anybody got any idea which troubles i could get with this configuration? San ________________________________ Do you know all add-ons for Microsoft CRM? Visit http://www.pimpmycrm.com The biggest dange...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

Emails #3
Hi, I was wondering if anyone knew of any web based email provder that I could use that wont be bloked but the I.T Filer at my work. I require use of emails during the day for personal use but work emails are monitored. I have tired various sites i.e Hotmail, Yahoo, Gmail, lycos etc but they are all blocked. Does anyone know of any that may not be picked up buy the web filer. Fiona Fiona, It is difficult to answer because it depends on what your filter is and how it is monitored. For instance, if it is actively monitored and somebody found out you were accessing the site, then it coul...

Outlook 2003 Drag and Drop Emails
I have an issue where there is a SBS 2003 server (newly installed) & when I drag emails to the file system (explorer window) in order to create file records of the emails it generates an error. Dialog Box Name: Error Copying File or Folder Error Msg: Not enough storage is available to process this command. I can't find an error logged anywhere, either on the server event logs or on the local machine event logs... I have searched the MS KB & Office online, but no joy yet... If anyone can help that would be great!!! R ...

setup Windows Mail as Word 2003 default emailer
All I can do is setup Outlook. I do not use Outlook. I would like to email Word docs using MS Windows Mail (new version of Express) In the Windows Start area, type Regedit into the search bar and then start the Registry Editor and go to HKEY_CURRENT_USER>Software>Clients>Mail Right Click on the (Default) item and then on Modify and in the Value data: field enter Windows Mail so that after you click OK, you have (Default) REG_SZ WIndows Mail -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a pa...

message delivery problems
Hello! There are several email address that when I send them an e-mail, they just sit in the Queue. I did message tracking and its telling me that "Message Routed and Queued for Remote Delivery". Can anyone suggest symptoms for this? Thank You! usually messages sitting in outgoing queue take time due to DNS related issues. Do you have issues performing NSlookups on any domains from your workstation? Can you perform the same NSlookups from the exchange server? From the exchange server, can you perform smtp communication directly to the mailserver that would receive the e...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

Having problem with spoofing email
Our users just received multiple email from different users outside the company. In the To: line, it shows his user name correctly but when he print those email, the To: line was showing somebody else name on the print out. Is there a way to block this behavior? I'm using E2k3. For some reason our spam (postini) didn't pick up these emails. Thank you, Could you please post the message in raw format (including the mail headers) Petch wrote: > Our users just received multiple email from different users outside the > company. In the To: line, it shows his user name cor...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

outlook 97 and express email problems
Hi, I am currently on an IBM X21 laptop and is running windows 98 se with office 97 pro. I recently experienced some problems with outlook (illegal operations etc) and reinstalled office to fix the problem but since then I have not been able to send or recieve emails with outlook 97 and outlook express 6. I simply get an error message saying the host can't be found (but does exist and I can ping it successfully). Any suggestions on what I might do? I have tried creating new accounts in windows mail and outlook express, but I still get the same error. Thankyou in advance! Tim D...