If not all cells have data prohibit from saving

**Rachel5694** · 10-24-2018, 07:02 PM

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.

**Winon** · 10-25-2018, 01:22 AM

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.

**Winon** · 10-25-2018, 03:13 PM

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.

**Rachel5694** · 10-25-2018, 04:04 PM

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.

**Rachel5694** · 10-25-2018, 04:06 PM

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 ?

**Winon** · 10-25-2018, 11:36 PM

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.

**Rachel5694** · 10-26-2018, 04:35 PM

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.

**Winon** · 10-26-2018, 04:51 PM

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.

**Rachel5694** · 10-26-2018, 06:10 PM

Hm the workbook seems to be blank?

**Rachel5694** · 10-26-2018, 06:20 PM

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?

**Winon** · 10-26-2018, 06:22 PM

Hello Rachel5694,

Please try this one.

Hope it helps.

**Winon** · 10-26-2018, 06:36 PM

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.

**Rachel5694** · 10-28-2018, 10:27 PM

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.

**Rachel5694** · 10-31-2018, 12:16 AM

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.

**Winon** · 10-31-2018, 12:35 AM

Hello Rachel5694,

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

Regards.

**Winon** · 10-31-2018, 12:53 AM

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.

**Winon** · 10-31-2018, 12:00 PM

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.

**Rachel5694** · 11-02-2018, 10:34 PM

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

**Winon** · 11-03-2018, 01:16 AM

Hello Rachel5694,

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

Thank you for marking this Thread as "Solved".

Regards.

**Winon** · 11-03-2018, 12:41 PM

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

If not all cells have data prohibit from saving

LinkBack

Thread Tools

Rate This Thread

Display

If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Re: If not all cells have data prohibit from saving

Thread Information

Users Browsing this Thread

Similar Threads

Help on Macro to Prohibit saving the workbook

Require input data in certain cells if other cells are filled before saving

[SOLVED] Prohibit Saving an Excel Files if Conditions not Completed

Prohibit data entry based on adjacent cell

Prohibit sorting in a Worksheet

[SOLVED] Prohibit users from modifying

prohibit closing a workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions