+ Reply to Thread
Results 1 to 10 of 10

stoping duplicate record of data with double click of macro button

  1. #1
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    stoping duplicate record of data with double click of macro button

    Hello all,

    beginner to VBA so please bear with me,

    I have been tasked to write a macro for a purchase order form.

    I have attached the file to help you guys understand and see how much of mess I got myself into.

    I have currently written code so all active cell data is picked up and recorded on to a separate data sheet via a click of a button,

    I have found if this button is accidentally double clicked this will recorded the data twice.

    I am lost of how to prevent this.

    please see attached the work sheet I am working on Macro PODATA

    I understand I my current work might be the long way round but all of this has been hand typed and not recorded.

    IF you could help me that would be amazing as I have been working on this for weeks and being chased up now.

    Thank you in advance for tips and help
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: stoping duplicate record of data with double click of macro button

    Sorry forgot to mention the Variable to I would like the duplicate to be checked against is the Cell PO_OrderNumber vs that on Podata of Po ordernumber

  3. #3
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: stoping duplicate record of data with double click of macro button

    Morning,

    Could any one please help me on this?

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: stoping duplicate record of data with double click of macro button

    ok i did a simple work around
    in Cell Q2 i put this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then in your POData code i put this line at the start of the code
    Please Login or Register  to view this content.
    this will make sure that the PO number does not exist in POdata sheet
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: stoping duplicate record of data with double click of macro button

    Thank you Humdingaling,

    This works a treat , one more question How would I introduce a message box "The Order number already exists please type a new order number"

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: stoping duplicate record of data with double click of macro button

    replace the code above with this
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: stoping duplicate record of data with double click of macro button

    ps

    i see what you tried to do with the VBA code in POCheck
    if you really want to go down that route i can have a look tomorrow
    you are on the right path, it just takes a bit more coding.

  8. #8
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: stoping duplicate record of data with double click of macro button

    Thank you very much again,

    And that means a lot that I was on the right track as this is my 1st attempt at writing a macro, I just find it hard knowing what command to use for each simple task.

    I understand you my code is very long and I am sure there is much shorter way.

    my next task is now that we have the data backed up in PO Data, is if on the buyers need to recall past Purchase orders they can type in Order Number box and recalls all the data. Is this the reverse of what I have just done to record the data or would I need to start from scratch?

    Regards,

    Aj

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: stoping duplicate record of data with double click of macro button

    if you want to recall a PO

    type in the PO number into the field then write the code to retrieve all the fields from PO_Data sheet

    ill give you a hint to get you going
    Q2 already has the row number
    if Q2 is NA PO does not exist exit sub (like previous code but opposite)
    if it returns a number use that as your reference row

    Please Login or Register  to view this content.
    heres some code to get you started
    Last edited by humdingaling; 05-14-2014 at 10:43 PM.

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: stoping duplicate record of data with double click of macro button

    if you wanted it exclusive in VBA

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Changing cell properties with double click, then revert with another double click
    By mweber2525 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-01-2014, 01:40 PM
  2. macro to prevent double record data
    By oxxy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-13-2013, 05:58 PM
  3. WHAT TO CLICK ON TO DETECT END OF DATA IN A MACRO? Need to enter new record at end of data
    By JohnExcel2010 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2012, 09:59 AM
  4. [SOLVED] Double Click Spin Button
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-27-2012, 02:40 PM
  5. Double Click Button
    By joshygoodboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-03-2010, 03:05 AM

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