Force user to fill out cells before clicking the magic button.

  • Good afternoon Experts! :)


    One worksheet 1, the user must fill out specific cells (D6, D8, D10, D12, D14, D16, D18, D20, D23, D26, D29). Once all filled out, they can click the ADD NEW button and the content is added to a second worksheet in columns A to K.



    • How can I force the user to fill out all 11 cells in order to click the ADD NEW button and move the content to column A to K of worksheet 2? I want all the data fill out, not just a few cells.
    • How do I add a code to include the user name and the time / date when entry is added to worksheet 2 (when the button is clicked)? I would like to add it to column L and M of worksheet 2.


    Thank you for your help. :sing::sing:




  • Re: An easy one : Force user to fill out cells before clicking the magic button.


    Try by adding this type of code to the beginning of your existing code


    Code
    If isnull(range("D6")) or isnull(range("D8")) or isnull("range("D10")) or .....add additional ranges... then
    MsgBox ("Missing Data")
    Exit Sub
  • Re: An easy one : Force user to fill out cells before clicking the magic button.


    Hi,


    I revised the code to this :



    I am no longer getting my SAVED message when I fill out all the cells and click the ADD NEW BUTTON and I am not getting the Missing Data message box when I leave an empty cell and click the ADD NEW BUTTON .... :/

  • Re: An easy one : Force user to fill out cells before clicking the magic button.


    .


    Presuming everything worked fine in your original macro, here is a small add-in that should warn the user to fill in all fields.
    Let me know how it works.



  • Re: An easy one : Force user to fill out cells before clicking the magic button.


    Try replacing your complete code with this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Force user to fill out cells before clicking the magic button.


    Thank you very much everyone. I woke up to solutions :)


    I used the bit of code from kjBox.

  • Re: Force user to fill out cells before clicking the magic button.


    You're welcome. Pleased the code worked for you.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Force user to fill out cells before clicking the magic button.


    Is there a way to lock the results worksheet so no one can remove data except the admin who can lock and unlock it? Thanks :)

  • Re: Force user to fill out cells before clicking the magic button.


    You need to do the following


    1. Go to the Results sheet and protect it using whatever password you want


    2. Change the code for updating the Results sheet to this


    3. With the code change the password where indicated (currently "poulet") to exactly the same as the password you just used to protect the Results sheet, remember passwords are case sensitive.


    4. Save the file.


    5. Password protect the VBA from viewing (so users cannot open the VB Editor to get the sheet password). To do this you need to open the VB Editor (alt + f11), click Tools in the menu bar, select "VBAProject - Project Properties", select the "Protection" tab on the window that will pop up, check "Lock Project for Viewing", enter and confirm password (does not have to be the same as the sheet protection password), click "OK".


    6. Save the file again.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Force user to fill out cells before clicking the magic button.


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Force user to fill out cells before clicking the magic button.


    Hi kjBox.


    How would I log the name of the user and the time of the entry (when the macro button is pressed) in the two last columns of the results page?


    Ex : column : John Smith column : August 15, 2017

  • Re: Force user to fill out cells before clicking the magic button.


    Use this, remember to change the password in the code if needed.


    This will put the user's computer "User Name" in column 12 of the Results sheet, if you want actual names then you will have to have a list, on a hidden sheet, of users' usernames and their real names. Let me know if that needs doing.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Force user to fill out cells before clicking the magic button.


    I get a syntax error with the following line

    Code
    .Cells(1row, 13).NumberFormat = "mmmm DD yyyyy"


    Thank you :)

  • Re: Force user to fill out cells before clicking the magic button.


    I am not sure why you should get that error, here is a test file with the code working, can you try it please.

  • Re: Force user to fill out cells before clicking the magic button.


    I just noticed something, you have

    Code
    .Cells(lRow, 13).NumberFormat = "mmmm DD, yyyy"


    that should be

    Code
    .Cells(lRow, 13).NumberFormat = "mmmm dd, yyyy"


    dd not DD

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Force user to fill out cells before clicking the magic button.


    Nice catch. I'll change it and see if it will fix it. Thanks! :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!