+ Reply to Thread
Results 1 to 10 of 10

prevent userform double entries via vba coding

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    prevent userform double entries via vba coding

    Hello-I'd sure appreciate any and all help regarding the following problem I'm having. I have a userform that has multiple types of data entry (i.e. combo boxes, text boxes, etc). Upon the user clicking on the "enter" button, which is named "CommandButton1, the data will be placed in the next available line/row and placed in the appropriate columns,etc. The problem I am experiencing is that sometimes the persons entering data accidentally double click the "enter button" or sometimes get distracted and then re-strikes the "enter button" forgetting if they had done so prior and my worksheet is getting multiple entries of duplicate data. I have tried to search this site for prior code that might help in my case without luck. I'm not at all knowledgeable about vba coding and can struggle through some coding I've seen others put together and the following is my attempt to do so; without success. Any assistance would be greatly appreciated!
    Please Login or Register  to view this content.
    Last edited by lilsnoop; 08-06-2011 at 05:55 PM. Reason: updating status

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: prevent userform double entries via vba coding

    hi, try to change this line:

    Please Login or Register  to view this content.
    to this one:

    Please Login or Register  to view this content.
    Another point though it will not make any difference: it's enough to use On error resume next once in the code and it will work either until On error GoTo 0 or code end.
    Last edited by watersev; 08-06-2011 at 04:07 PM.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: prevent userform double entries via vba coding

    When I use forms to enter data into a database, I always use this to prevent duplicate entries:

    Upon clicking the ADD DATA button, the macro does the following:

    1) Checks to make sure all required form boxes are filled in
    2) Fills in the data to the next available row in the database
    3) Clears all the data from the form before exiting

    If someone "double clicks" the ADD button, the second ADD would find a blank form.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: prevent userform double entries via vba coding

    Thanks Watersev, I tried that and even if I click the "enter button" just once I get the message I'm trying to enter duplicate data. So I'm not sure why it is doing that. But on the positive side it does prevent duplicate data as it appears to overwrite the previous existing line of similar data.

    Jerry, I like your suggestion too. I do have a "clear data" button as well, but if I can't get the code above to work, your suggestions may be the best way to go.

    Thanks for taking time to look at this problem!
    Last edited by lilsnoop; 08-06-2011 at 04:31 PM.

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: prevent userform double entries via vba coding

    can you post workbook with the userform?

  6. #6
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: prevent userform double entries via vba coding

    it gives you that message because:
    1. It looks for TxtComplaint.Value in V column
    2. The value is found so TxtComplaint.Value equals to ""
    3. If TxtComplaint.Value equals to "" you get a message: "You're trying to enter this data twice-Which I've Prevented"

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: prevent userform double entries via vba coding

    If you already have a "clear data" button, then you could just call that existing macro from inside your ADD button code at the correct point.

  8. #8
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: prevent userform double entries via vba coding

    Waterserv-I've zipped the file. So I hope it is small enough for you to get the idea of what it is doing. It is in xlsm format from Excel 2007.

    Jerry-I think I understand what you are saying the code is doing. How would you rewrite the code to bring the message for only double clicks or duplicate entry attempts. Or do you think I just need to eliminate that message as the existing code will prevent the duplication of data anyways?
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: prevent userform double entries via vba coding

    Jerry's idea would be the best to apply here:

    Changes required are:

    1. Private Sub CmdRefresh_Click(): add turning on/off events while clearing the form

    Please Login or Register  to view this content.
    2. Private Sub CommandButton1_Click(): adding Call CmdRefresh_Click to trigger clearing the fields after Enter button is pushed once


    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: prevent userform double entries via vba coding

    Thanks so much to the both of you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1