+ Reply to Thread
Results 1 to 20 of 20

If not all cells have data prohibit from saving

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Question If not all cells have data prohibit from saving

    Hi I really need help on this VBA code, any suggestions would be great, Thanks!

    For each row, see below, all 5 columns need to be filled in. If a user tries to save the file without all the columns, ONLY in that particular row, filled in then an error message will pop up prompting them to fill the blank cells in.

    VBA Help.PNG

    I was experimenting and was able to produce the message just for one cell but I can not figure out how to do what I described above.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    If a user would enter a space in any of those cells it will be regarded as a non-blank cell, which I believe you would want to avoid. So, below is Code for one way of doing this, where the Code will remove any leading or trailing spaces, and return you to the relevant empty Cell, if any.

    Please Login or Register  to view this content.
    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello again Rachel5694,

    I was wondering if you had things under "control" when trying to Close the Workbook with the same checks. If so, then please add the code below to the ThisWorkbook Before Close Event.

    Please Login or Register  to view this content.
    Regards.

  4. #4
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Never thought about the space not being recognized as an empty cell, thank you! This worked however, if all G:16 - K:16 are all empty I don't want it to produce the saving error message. I only want it to allow a save if some of the cells are filled but not all are. Kind of like an all or nothing type thing, either all cells in the range have to be filled in or none of them can be filled in.

  5. #5
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Is there a way to do like If Range ("G16:K16") = "" Then move on to next range of G17:K17, Else if the code you created above ?

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Thank you for your response Rachel5694,

    Below you will find all the Code you need in the ThisWorkbook Event, as per your revised requirement.

    Please Login or Register  to view this content.
    Regards.

  7. #7
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Thank you But that still won't let me save for range G16:K16 if all the cells are blank. So, if all the cells in the range are blank I would like it to allow me to save. I only don't want it to allow me to save if some of the cells in that range are filled in but not all.

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    It works just fine on my side.

    Please try the attached sample Workbook.

    Edit: Open the Workbook and save it on your system, then exit Excel completely and reopen the Workbook again.

    Regards.
    Attached Files Attached Files
    Last edited by Winon; 10-26-2018 at 05:15 PM. Reason: Provided more info

  9. #9
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Hm the workbook seems to be blank?

  10. #10
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Oh wait never mind the code you provided is working correctly for me now!! Thank you!! If I wanted to produce this so it applies to all the rows 16-58 would I have to retype out that code for each line? or is there a way to loop it?

  11. #11
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    Please try this one.

    Hope it helps.
    Attached Files Attached Files

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    If I wanted to produce this so it applies to all the rows 16-58 would I have to retype out that code for each line? or is there a way to loop it?
    That would just about be impossible, since a loop will look at any Empty Data, and pepper you with message boxes, which will be very annoying. Please give me some time to see if I can find a workaround on that in a day or so.

    If you think about it again, assume all the rows are empty, or the third row down is empty, how would the loop know which row to select. I.M.O. I don't think a loop will work. unless someone else on this Forum has a better idea.

    Kind regards.

  13. #13
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Thank you so much for looking into it!! I really appreciate the help on this. I might just have to manually enter in the code for all the rows.

  14. #14
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    I am trying to Duplicate the code for more rows. I entered what I have below, it works for row 16 still but not for row 17. Any ideas or suggestions?

    Please Login or Register  to view this content.

  15. #15
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    This Post somehow duplicated while trying to edit it. Please see Post #16 for correct Code.

    Regards.
    Last edited by Winon; 10-31-2018 at 12:56 AM. Reason: Post duplicated while Editing

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    Edit Code

    You require one more "End If's" as shown in Red in the Code below.

    From there you may continue coding for all the other Cells.

    Please Login or Register  to view this content.
    Regards.

  17. #17
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    Please see the attached sample Workbook, in which I have done three Lines for you.

    The revised Code is shown below.

    Please Login or Register  to view this content.
    Regards.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: If not all cells have data prohibit from saving

    Thank you so much!!! I have successfully replicated the code to row 58

  19. #19
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    Well done! You are welcome, glad I could assist.

    Thank you for marking this Thread as "Solved".

    Regards.

  20. #20
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: If not all cells have data prohibit from saving

    Hello Rachel5694,

    You asked me if there is a shorter way of doing this by maybe using a Loop, which threw me off track completely.

    Please don't do that to me.

    It bothered me, until I managed to come up with this MUCH SHORTER code.

    Please Login or Register  to view this content.
    Enjoy.

    Regards
    Attached Files Attached Files
    Last edited by Winon; 11-03-2018 at 01:12 PM.

+ 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. Help on Macro to Prohibit saving the workbook
    By Big_Slick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2014, 04:41 AM
  2. Require input data in certain cells if other cells are filled before saving
    By Jenkins86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 08:07 PM
  3. [SOLVED] Prohibit Saving an Excel Files if Conditions not Completed
    By piermyn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-20-2013, 08:46 PM
  4. Prohibit data entry based on adjacent cell
    By davemus in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-15-2013, 03:30 AM
  5. Prohibit sorting in a Worksheet
    By Carlsbergen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2009, 04:00 AM
  6. Prohibit users from modifying
    By Cath in forum Excel General
    Replies: 2
    Last Post: 07-21-2006, 10:42 AM
  7. [SOLVED] prohibit closing a workbook
    By Christophero in forum Excel General
    Replies: 2
    Last Post: 04-04-2005, 01:06 PM

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