Prevent Excel workbook closing saving if the mandatory cells not filled in

**pearlite007** · 02-07-2017, 07:23 AM

Hi All,

I am new to this forum - greetings to everybody here. Please kindly help me with the below mentioned query.

My query:

I have Excel workbook which needs to be filled in correctly. If user fills in any cell of column B, then he/she would need to fill in corresponding cells (same row) of columns E, F, I, J, K, L, & Q respectively. If the cells are not filled in, user will not be able to save or close the Workbook (active sheet). I am not good at visual basic, your help is highly appreciated. Please also guide me on if need be how do I add more cells of other columns than mentioned above.

I tried to attach the documents, not sure if it will work.

Kind regards,

Table screenshot.docx
FIR ladders 2016 2.xlsx

**BillyRaySpivy** · 02-07-2017, 07:44 AM

I will take a look at the file and see what I can do.

**pearlite007** · 02-07-2017, 07:49 AM

Here it goes. Please let me know if it opens.

FIR ladders 2016 2.xlsx

**pearlite007** · 02-07-2017, 07:51 AM

Thanks Billy.

**kev_** · 02-07-2017, 08:11 AM

welcome to the forum

...and you can try this too
workbook attached

VBA is in the workbook module

Please Login or Register  to view this content.

**BillyRaySpivy** · 02-07-2017, 08:18 AM

Kev,

Would it be possible to highlight the cells red until they are filled to remind the user?

**kev_** · 02-07-2017, 08:24 AM

I realised after posting that reference to the worksheet was omitted

(it worked - but could cause a problem if workbook contains more than one sheet!!)

So replace the code for ValueCheck with:

Please Login or Register  to view this content.

**pearlite007** · 02-07-2017, 08:35 AM

Kev,

Many thanks for the sheet - Genius! Couple of questions:

1. I replaced the code with the one you sent just now, received compilation error.
2. If I change the name of sheets in the workbook or copy the same sheet in the workbook, will the code be working on those sheets as well?

Could you kindly replace the coding and send the workbook to me.

Best regards,

Kash

**kev_** · 02-07-2017, 09:35 AM

This is the code amended to make the empty cells RED
Try in attached workbook

Note that VBA now uses SpecialCells(xlCellTypeBlanks) to identify empty cells without looping through them

Please Login or Register  to view this content.

**BillyRaySpivy** · 02-07-2017, 09:55 AM

I tried the updated code to make the cells red and nothing seems to happen. If you have a chance could you please check it out?

**kev_** · 02-07-2017, 10:00 AM

????

I have downloaded the file that I posted and it works fine for me
Are you opening the correct file after download?

**BillyRaySpivy** · 02-07-2017, 10:03 AM

Sorry about that it was the wrong file. Thank you for being such a great help.

**BillyRaySpivy** · 02-07-2017, 10:05 AM

I did try it again and noticed that if the contents of the cells are emptied the ranges stay red. It might be useful to have another condition if the user decides to delete the contents.

**kev_** · 02-07-2017, 10:23 AM

No problem. We all do it!

Cells only stays red until you try to close or save file again - keeping the red there should remind the users to get it right next time!!

I suggest you turn the problem on its head before things get messy:

How about:
- when value is input in column B THEN
- the critical cells are formatted red UNTIL
- a value is placed in each critical cell, and its formatting returns to normal

And trigger the "before close" and "before save" with the rule that any red cells results in no close/no save

**pearlite007** · 02-07-2017, 11:04 AM

Hi Kev,

Many thanks for your genius work - It looks good. If I copy/move the same sheet in the workbook or change the name of the sheet in the workbook, will the code still work?

Regards,

Kash

**kev_** · 02-07-2017, 11:57 AM

see post#9 - the clue was in this line:

Please Login or Register  to view this content.

Will there be more than one sheet in the workbook to which this code needs to apply?
If there is, then need to loop through all the sheets that apply.
Let me know...

thanks
Kev

**pearlite007** · 02-07-2017, 04:42 PM

Hi Kev,

Thanks - Yes there will be more than one sheet in the same workbook, one sheet per week.

Best regards,

Kash

**kev_** · 02-07-2017, 05:09 PM

Ok - are there any sheets that should be excluded?

**pearlite007** · 02-07-2017, 05:12 PM

No, there are no sheets that would need to be excluded.

Many thanks.

Prevent Excel workbook closing saving if the mandatory cells not filled in

LinkBack

Thread Tools

Rate This Thread

Display

Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Re: Prevent Excel workbook closing saving if the mandatory cells not filled in

Thread Information

Users Browsing this Thread

Similar Threads

Mandatory Fields and Closing/Saving Workbooks

Prevent saving unless the data is filled

[SOLVED] Prevent a workbook from closing if any cells are unlocked

VBA to prevent saving if any cells are filled with a certain color

Mandatory cells if another is filled

[SOLVED] How to make it IMPOSSIBLE TO CLOSE Workbook until mandatory cells r nt filled?

[SOLVED] Prevent closing workbook unless specific cells have data

Bookmarks

Bookmarks

Posting Permissions