Need code to make completion of certain cells mandatory before saving file

[SBeirne]

Hello, Thanks in advance for reading this and any help provided. I'm new to VBA so any help is appreciated!

I need to make a form unable to be saved before all the required cells are filled in. It's for work and we've a problem with people sending in request missing required information.

The required cells are: F10,G10,F11,F14,F15,F19,F20,F21,F22,F23,F24,F25,F26,F28,I28,F32,F35,F37,F40,F41,F42,F44,F45,I45,F46,F47

Some of these are merged cells, would that make a difference to the code required? e.g. G10 is a merged cell made up of G10,H10 & I10.
A number of cells contain drop down menus (F10,F24,F25,F42,F44,F45,I45,F46,F47) so would that matter?

I have the code that works for cell G10 but don't know how to edit it properly to make it so all the other required cells are also checked. The sheet in question is called CCL.

Please Login or Register  to view this content.

[JBeaucaire]

Here's an idea you can play around with. Put this formula in a cell off to the right... say M10.

=IF(COUNTA(F10:I48)-COUNTIF(F10:I48, "*drop down*")=36, "DONE", "NOT DONE")

Then have your BeforeSave macro check that cell, needs to say "DONE".

[wallyeye]

I don't think it will make a difference with the merged cells or the dropdowns. Merged cells will report the value in the upper-left most cell, dropdowns record their value in the cell. You could do something like:

Please Login or Register  to view this content.

Define a Named Range "RangeLabels" that points to a range containing the cell identifier in the first column and its description in the second, like:

F10 EmployeeNumber
G10 Employee Name
F11 Department
F14 F14
F15 F15
F19 F19
F20 F20
F21 F21
F22 F22
F23 F23
F24 F24
F25 F25
F26 F26
F28 F28
I28 I28
F32 F32
F35 F35
F37 F37
F40 F40
F41 F41
F42 F42
F44 F44
F45 F45
I45 I45
F46 F46
F47 F47

[SBeirne]

Thanks JBeaucaire! I'll play around with this and see if I can get anywhere.

[SBeirne]

Thanks wallyeye! It's smarter to have a named range than to have to list all those cells. I'll try this and see how I get on.