+ Reply to Thread
Results 1 to 9 of 9

vba, Ensure all fields are filled before transfering to excel

  1. #1
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    vba, Ensure all fields are filled before transfering to excel

    Hello,

    I have a userform with multiple textbox and combobox.

    my add-button transfers the data to excel and here is the code:

    Please Login or Register  to view this content.
    Now I want to make sure that all fields are filled before transferring to excel except SubSubCatcombobox which can be empty. I tried if then but it didn't work for comboboxes and it worked only for each textbox individually.

    Please Login or Register  to view this content.
    The Cat and Subcat and Sub-Subcat are multilevel dependent combobox.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: vba, Ensure all fields are filled before transfering to excel

    Are the 6 controls mentioned in your first bit of code the only Combo and Text boxes on the userform?
    If so you could use something like the below. Not tested as no sample workbook provided.
    Please Login or Register  to view this content.
    BSB
    Last edited by BadlySpelledBuoy; 09-11-2020 at 04:28 PM. Reason: Fixed a typo in the VBA

  3. #3
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: vba, Ensure all fields are filled before transfering to excel

    There are 3 textbox and 4 combobox (including SubSubCat) for now. I'm continuing the userform, so can you please explain me the code so I can change it if I had to add more items to it?

    I tried the code and I get this error : "Compile error: Variable not defined" and it marks the control in
    Please Login or Register  to view this content.
    Sorry I can't upload the excel file because it has some confidential information.
    Last edited by (T_T); 09-11-2020 at 04:03 PM.

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: vba, Ensure all fields are filled before transfering to excel

    It loops through all controls on the form, regardless of type. If it's a TextBox or a ComboBox called anything other than SubSubCatComboBox then it checks if it contains a value.
    If doesn't then it adds 1 to a variable (ErrCnt) that counts how many errors there are. An error = a blank control that fits the above criteria.

    Once it's looped through all the controls, if the error count is more than zero, i.e. it found a blank control, then it will show the message. If the error count = zero then it will perform your code to stamp the data to the worksheet.

    If you're adding more Text or Combo boxes that willl need to be checked if they're blank then you shouldn't need to amend the code at all. If they need to be excluded from the error checking then you can add those quite easily to this line:
    Please Login or Register  to view this content.
    or as separate IF statements within that IF/END IF block.

    Does that help?

    BSB

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: vba, Ensure all fields are filled before transfering to excel

    Apologies, there's a typo in my VBA.
    Change this line:
    Please Login or Register  to view this content.
    To this:
    Please Login or Register  to view this content.
    I've amended the code in my post above.

    BSB

  6. #6
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: vba, Ensure all fields are filled before transfering to excel

    Quote Originally Posted by BadlySpelledBuoy View Post
    It loops through all controls on the form, regardless of type. If it's a TextBox or a ComboBox called anything other than SubSubCatComboBox then it checks if it contains a value.
    If doesn't then it adds 1 to a variable (ErrCnt) that counts how many errors there are. An error = a blank control that fits the above criteria.

    Once it's looped through all the controls, if the error count is more than zero, i.e. it found a blank control, then it will show the message. If the error count = zero then it will perform your code to stamp the data to the worksheet.

    If you're adding more Text or Combo boxes that willl need to be checked if they're blank then you shouldn't need to amend the code at all. If they need to be excluded from the error checking then you can add those quite easily to this line:
    Please Login or Register  to view this content.
    or as separate IF statements within that IF/END IF block.

    Does that help?

    BSB
    Yes Thank you! This helps a lot (^_^)

  7. #7
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: vba, Ensure all fields are filled before transfering to excel

    Quote Originally Posted by BadlySpelledBuoy View Post
    Apologies, there's a typo in my VBA.
    Change this line:
    Please Login or Register  to view this content.
    To this:
    Please Login or Register  to view this content.
    I've amended the code in my post above.

    BSB
    I tried it, it works for the textbox but not for the combobox (all of them)

  8. #8
    Forum Contributor
    Join Date
    05-05-2020
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    100

    Re: vba, Ensure all fields are filled before transfering to excel

    I had to change the "Combobox" to "ComboBox" in this line
    Please Login or Register  to view this content.
    Thank you

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: vba, Ensure all fields are filled before transfering to excel

    I'm writing the code without a workbook to test it on so some slight errors...

    Needed to move the brackets in this line:

    Please Login or Register  to view this content.
    Again, code amended in post #2

    BSB

+ 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. Replies: 1
    Last Post: 12-01-2017, 08:34 AM
  2. [SOLVED] Ensure userform fields are filled in before continuing
    By TPDave in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2014, 09:09 AM
  3. Ensure selected cell is filled before proceed to VBA language
    By alexnkc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2013, 09:17 PM
  4. [SOLVED] Ensure TextBoxes are Filled in VBA Forms
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-07-2012, 04:18 PM
  5. How to ensure all form fields are completed.
    By singerbatfink in forum Word Formatting & General
    Replies: 3
    Last Post: 02-23-2012, 06:19 AM
  6. Ensure TextBoxes are Filled in VBA Forms
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2011, 09:30 PM
  7. Ensure userform fields are complete
    By pkling in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-23-2010, 06:48 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