+ Reply to Thread
Results 1 to 14 of 14

Validation of Input data - Will VBA work?

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Exclamation Validation of Input data - Will VBA work?

    Hello, I have a project in which I have created multiple QR codes to be scanned into a spreadsheet to identify when specific tasks are completed. This will occur Monday Through Friday 24 hrs. per day. There is a total of 210 codes and each are unique to a particular line and machine. If a code is scanned in more than once, it will generate an error message to let them know that duplicates are not acceptable. This is where my problem presides. If an error message is acknowledged and the operator clicks OK, it will automatically clear the field and they can re-scan to QR code and it will be fine, however, if they are not paying attention and scan another code without acknowledging the error message, it will clear the error and only enter the latter portion of what the QR code is programmed to do, which will not be identified properly and will not show as being completed.
    Example: TIR01-1-PCI is an acceptable entry, when an error has not been cleared, it may only show a partial scan of PCI, which is the last part, but incorrect.
    I have tried to utilize Data Validation, but when using a scanner, it will over-write the validation rules. I have looked in multiple areas of the web to see if data validation could be completed by using VBA code after the scan was entered and generate an error if it does not match something in a predefined list, but unable to find anything to accommodate. I have tried to place to code to clear the field before the error message is generated, but still get partial scans at times.
    This does not have to be a data validation if I can have certainty that partial scans will not occur. I am open to alternate suggestions as well and I do appreciate any input provided.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    Hi Nedap,
    You can generate Data Validation codes using VBA, you will have to in some what determine the criteria.
    Using a peronally recoded macro I came up with a working solution (see code blow)
    Please Login or Register  to view this content.
    What is doe is look though a predefined range and add the value to to the variant qrange which at the end is used as forumla for the data validation.
    Play around with it the worskheet names and so you will have to edit but it's the main idea that counts.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    Look up the post by jorel (Lotomania) I built it in there for him

  4. #4
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Validation of Input data - Will VBA work?

    Keebellah, Thank you for the response, I will work with this code and see what I can accomplish.

  5. #5
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Validation of Input data - Will VBA work?

    Hello Keebellah, Thank you for the information, I added code to my project and modified to suit.
    I can get it to work, but when I save and re-open file, I am getting error from excel and have to recover file, but do not get everything back.
    I changed the range to select only 10 cells for validation and everything worked fine. I can close and re-open with no issues.
    When I increase the range to 20 cells, I get the error again.
    When I try to upload copy of code, my server is blocking me due to content (security of our company does not allow code to be sent),
    so I will send copy from home later.

    Excel Error.PNG

    Thank you also for the additional thread to see previous work of jorel (Lotomania),
    however, there are four pages of posts and I couldn't determine which one it was.

  6. #6
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Validation of Input data - Will VBA work?

    I have attached a word document with the code as I keep getting blocked when trying to post code directly.
    Attached Files Attached Files

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    If the code you posted is the same as in the word doc you forgot the first CODE in square brackets.
    The reason the posting is blocked is because you cannot include special characters, (and not inside code brackets) some are blocked because they 'trigger' other actions. If you just placed the vba code then it would have been no issue,
    About the LOTOMONIA it's the last post and the attached file
    The code
    Please Login or Register  to view this content.
    What's the Msgbox doing here? This is the problem

  8. #8
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Validation of Input data - Will VBA work?

    Hello Keebellah, The MsgBox was intentional so I could ensure that I had my files assigned correctly. When the line of code containing "MsgBox qRange" is removed, I am still getting the error after closing and re-opening the file. My codes is below.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    Is it possible to attach the file or does it contain private data?
    The idea I posted works good.
    With what you have posted I don't know what the problem is and what the exact error message is, have you tried going through the code step by step? What is the text in the cells you use maybe there's more than just text?

  10. #10
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Validation of Input data - Will VBA work?

    I have gone through the code line by line, but to no avail for resolution.
    I have attached a copy of the file (TPM Scans.xlsm) and with additional testing have determined that the line of code below:
    Please Login or Register  to view this content.
    needs the range to be set to ("A1:A210"), but when it is set to A1:A17 or greater is when I get the recovery error.
    As long as it is set to A1:A16 or lower, no problems.
    I have also played around with changing the file type from 2016 macro enabled workbook (xlsm) to 97-2003 workbook (xls) and it seems to be working fine.
    There is another file attached (TPM TIR QR Codes.xlsx), which contains all of the QR codes used to scan data.
    These QR codes match the data found on the third tab (TIRDescription) contained within the TPM Scans file.
    Attached Files Attached Files

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    Strange, cannot see i=the issue at first hand, will do some more trouble shooting.

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    My guess is that the datavalidation list is the problem but only when you reopen the file.
    I changed it around a little bit, the data validation is cleared before closing and with application ontime (macro Begin_Here) it is repopulated 2 seconds after the file is opened.
    Looks lie that helped.
    Saw similar issues and questions on other fora
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-13-2014
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    7

    Re: Validation of Input data - Will VBA work?

    I had also come to the conclusion that it has something to do with Data Validation.
    I have looked at the updated information provided and do see that it will work, but there is still a concern.
    Typically, this file would be open and running at all times, which would not be an issue.
    My concern is that if we have a power outage or someone closes the file inadvertently, that the data would be lost.
    The data for this sheet feeds into another workbook that is updated and broadcast on a television for a visual view of completed items.
    We also need to ensure that there are no duplicate entries, and that code is no longer included in the file, but I can add it back.
    Ultimately, at the end of the day, the data will be extracted into a MS Access Database to track historical data.
    Not quite to that point yet, but it all ties together to a much more elaborate scheme than just this workbook.

    I thank you very much for all of the assistance you have provided.
    I would prefer to not include all files into a public forum, but if you were willing to look at them in a private manner, I'll be more than happy to share with you.
    Please let me know if this is possible and how to go about doing and / or provide any additional input you may have.

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,561

    Re: Validation of Input data - Will VBA work?

    See PM I sent you

+ 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. data validation in an input box
    By Suraj3825 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2013, 01:35 AM
  2. Extract data into a master work book by accessing various input work sheets/workbooks
    By kammariarun in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-08-2011, 05:10 PM
  3. Data validation on an input box
    By pprseller in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2009, 06:05 PM
  4. Pop-up input boxes with data validation
    By tony0710 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2009, 10:42 AM
  5. Data from Input Box overrides cell input validation
    By Winon in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-01-2007, 02:29 PM
  6. [SOLVED] Data Validation list in Input Box... Possible ?
    By Corey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2006, 03:10 AM
  7. Data>Validation>Input message
    By irresistible007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-03-2005, 09:10 AM
  8. Data validation/Formatting input
    By Wescotte in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2005, 08:05 AM

Tags for this Thread

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