+ Reply to Thread
Results 1 to 12 of 12

'Force' users to enter data in all cells before emailing

  1. #1
    Registered User
    Join Date
    09-05-2005
    Location
    Basingstoke, England
    MS-Off Ver
    2003
    Posts
    86

    'Force' users to enter data in all cells before emailing

    I have a form that managers must complete in the event of an accident and then to be emailed. Currently I have got as far as having code triggered by a command button to email the sheet. I have cells that have validation data and conditionally formatted to reflect data that has been added to that particular cell. The problem is that users can still send the sheet without actually filling in any data, what I am aiming to do is for the command button not to work unless my range of cells has data entered into them.

    I have searched high and low but not come up with anything that may work (my vba knowledge is nigh on zero). Can anyone suggest anything that may prevent the sheet from being sent until data has been added to my range of cells?

    Many thanks in advance.

  2. #2
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: 'Force' users to enter data in all cells before emailing

    Hi Ducatisto,

    Try the attached, it is set up to prevent saving if certain cells aren't completed but you could modify to suit your needs.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-05-2005
    Location
    Basingstoke, England
    MS-Off Ver
    2003
    Posts
    86

    Re: 'Force' users to enter data in all cells before emailing

    Hello Dave and thanks for your response. I have just had a look at your code and it looks promising. I am out of the office now but will report back on Monday with hopefully positive results!

    Regards.

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: 'Force' users to enter data in all cells before emailing

    One thing I have done on a form of mine is to have a column somewhere that evaluates each field or range and returns a count. Then I just sum this column, and if the count is not appropriate, I display a message and then cancel the action.

    I write my formulas to evaluate to 0 if field meets the requirement, and 1 or more if input is still required.

    For instance, say we have a text field/cell that needs input, like a name field. The simple formula would be:
    =If(A1 = "", 1, 0)

    You could also modify it to include a dependancy on another field. Int he formula below, assume A1 is a field for hours worked. Cell B1 would be a reason for overage.

    =If(and(A1 > 40, B1 = ""), 1,0)

    For a range of cells, you could do something like:

    =Countif(A1:A10, "")

    Anyway, so you just construct a formula or series of formulas to test your form for required input. Then you just do a sum of the requirements testing column. If it is greater than 0, then input is required.

    You enforce this by using code like this at the beginning of your event procedure:
    Please Login or Register  to view this content.
    Last edited by Whizbang; 05-20-2011 at 08:32 AM.

  5. #5
    Registered User
    Join Date
    09-05-2005
    Location
    Basingstoke, England
    MS-Off Ver
    2003
    Posts
    86

    Re: 'Force' users to enter data in all cells before emailing

    Thanks for your replies but I have failed to get your code(s) to work. Here is my code attached to the Command button that sends the worksheet to recipients, somehow I need to add code that will 'remind' users to fill out the cells on the main sheet. I do not know if this is relevant or not but half of the cells have validated lists to pick a choice of answers from whilst other cells are for more information required so anything should be aloowed to be entered in those particular cells;

    Please Login or Register  to view this content.
    Many thanks for your help so far.

  6. #6
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: 'Force' users to enter data in all cells before emailing

    Mine is similar to Whizzbang's suggestion, check the sheet I posted, cell N1 returns True if any of the relevant cells are missing information, the code then uses the true or false in an If statement to determine the action -

    Please Login or Register  to view this content.
    It should just be a matter of altering the formula in N1 to reflect the data you need to monitor. The formula can go anywhere in your workbook so long as you adjust the code to the new location.

    Dave H

  7. #7
    Registered User
    Join Date
    09-05-2005
    Location
    Basingstoke, England
    MS-Off Ver
    2003
    Posts
    86

    Re: 'Force' users to enter data in all cells before emailing

    Quote Originally Posted by Dave H9 View Post
    Mine is similar to Whizzbang's suggestion, check the sheet I posted, cell N1 returns True if any of the relevant cells are missing information, the code then uses the true or false in an If statement to determine the action -

    Please Login or Register  to view this content.
    It should just be a matter of altering the formula in N1 to reflect the data you need to monitor. The formula can go anywhere in your workbook so long as you adjust the code to the new location.

    Dave H
    Dave, for some unknown reason when I add the formula to a cell (relevant to the cells I need data in), when leaving the cell the formula remains and not a true/false statement as per your example spreadsheet (thanks for that). As per my previous message I am not too sure if having validation lists in some cells or conditional formatting would affect the code (doubt it as I tried on cells away from my actual cells). I would agree that your solution is a sound one but somehow something is preventing the formula to act as it should (as it does in your example). Stumped here!

  8. #8
    Registered User
    Join Date
    09-05-2005
    Location
    Basingstoke, England
    MS-Off Ver
    2003
    Posts
    86

    Re: 'Force' users to enter data in all cells before emailing

    Addendum to the above. I went into Tools/Options and under View and Windows Options the Formulas field was ticked, unticking it and the formula worked as it should.

    Colin

  9. #9
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: 'Force' users to enter data in all cells before emailing

    Are you sorted now then? (my fingers are crossed)

  10. #10
    Registered User
    Join Date
    04-20-2011
    Location
    Mobile,al
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: 'Force' users to enter data in all cells before emailing

    Dave, suppose I only wanted to enforce this code within a certain time frame, say between midnight and 3am when the apreadsheet is supposed to be complete?There are certain entries made throughout the day on a spreadsheet of mine but isn't complete until the night crew inputs the last few readings in after midnight, so between the hours of midnight and 3am I'd like to make use of that same code but not each time the sheet is saved.Make sense?

  11. #11
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: 'Force' users to enter data in all cells before emailing

    Please see the forum rules

    Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

  12. #12
    Registered User
    Join Date
    04-20-2011
    Location
    Mobile,al
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: 'Force' users to enter data in all cells before emailing

    Thanks Dave!

+ 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