Looping through cell ranges in multiple worksheets and prevent saving if cell value empty

**Spritz** · 04-19-2013, 10:50 AM

Hi. I have a workbook with 8 sheets. I have 5 sheets that are called WO1, WO2, WO3, WO4, WO5. I want to loop through these 5 sheets and look at a certain range of cells to see if there is a value in any of those cells. IF there is and there is not a value in the next cell over, i want it to prevent saving and pop up with a message box asking the user to fill in all required fields in the worksheet that is missing information. Here is an example of my current code:

Please Login or Register  to view this content.

I tried defining strSection and strCustSparePart as a range and i got an "Object variable or With block variable not set" error on either line with the Choose function.

When i leave it as is i get an error on the CountA lines saying "Application-defined or object defined error". This is most likely because i am trying to call a string variable as a range?

Any help is appreciated!

**stnkynts** · 04-19-2013, 11:48 AM

The reason you got the error code when trying to define it as a range is because with objects and ranges you need to use the word "Set" like this

Please Login or Register  to view this content.

With that being said, if it were me doing this I would be approaching it completely different. Can you be more specific on which ranges you want to confirm there is a value and if there is a value, what range specifically cannot be blank. Please be very specific.

**Spritz** · 04-19-2013, 12:28 PM

Hi stnkynts,

Thanks for responding.

The exact ranges can be found above. I want it to look through each sheet for any values in the ranges of ("C26:C33", "J26:J33", "P26:P33") and IF THERE IS a value in say C26, there MUST be a value in G26. If there isn't i want it to show an error/msgbox when the user tries to save indicating they need to input information in G26.

So it goes as follows:

Looks for values in C26:C33 and looks for a corresponding value in G26:G33
Same with the others stated above in the loops. J/M, P/U

Does this make sense?

I ended up figuring most of it out on my own and everything is working as intended. I would like to fix the msg box so that it will display what value is missing a yes/no from which worksheet. This is my current code:

Please Login or Register  to view this content.

any ideas?

**stnkynts** · 04-19-2013, 05:25 PM

Try this: I didn't have time to test it nor write into a before save but i assumed based upon what you submitted you could figure that part out.

Please Login or Register  to view this content.

Edit: unfortunately it errored and I don't have time to figure out why. I will look at it when i get back this weekend.

**stnkynts** · 04-21-2013, 06:26 PM

Ok fixed it. I accidentally set the array as a string instead of variant. Give this a try:

Please Login or Register  to view this content.

**Spritz** · 04-22-2013, 09:47 AM

Hi stnkynts,

Thanks for your help so far!

So far this works great but i noticed that if you are missing information in multiple cells that it will only display a message box for the first one found. Is there a way i can get it to show individual messages for each one or one big one with all the missing ones?

Also for the message box, is there a way i can get it to say something like "Please choose Yes/No for Customer Spare Part for the part number " _____ and have it actually copy the value that is in cell C26? So people know what Part number it is for?

**stnkynts** · 04-22-2013, 10:33 AM

So far this works great but i noticed that if you are missing information in multiple cells that it will only display a message box for the first one found. Is there a way i can get it to show individual messages for each one or one big one with all the missing ones?

Yes. Just remove the 3 "Exit Sub". That line is what stops the code from going further to show all the empty cells.

Also for the message box, is there a way i can get it to say something like "Please choose Yes/No for Customer Spare Part for the part number " _____ and have it actually copy the value that is in cell C26? So people know what Part number it is for?

Yes. See below amended code.

Please Login or Register  to view this content.

**Spritz** · 04-22-2013, 11:24 AM

Thanks for all your help stnkynts!

Works perfect.

Here is the full code:

Please Login or Register  to view this content.

Looping through cell ranges in multiple worksheets and prevent saving if cell value empty

LinkBack

Thread Tools

Rate This Thread

Display

Looping through cell ranges in multiple worksheets and prevent saving if cell value empty

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Re: Looping through cell ranges in multiple worksheets and prevent saving if cell value em

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions