code to force cell entry

[3earth]

The following cells have data validation with list of (4,3,2,1). I need the user to have to select from the list before the document can be saved.

D8:D22
D24:D37
D39:D46
D49:D58
D60:D82
D85:D121
D124:D155

I have conditional formatting on the cells also. Red if blank.

Thank you for any help.

[dilipandey]

HI 3earth,

Look at below link from Microsoft:-

http://msdn.microsoft.com/en-us/libr.../ff840057.aspx


Regards,
DILIPandey
<click on below * if this helps>

[3earth]

This is what I need! Thank you

[3earth]

How do I save the file with the cells blank so others have to complete the cells?

[dilipandey]

you can take out those cells from the range you specified in the above code.



Regards,
DILIPandey
<click on below * if this helps>

[3earth]

I do not understand what you mean. Could you tell me step by step how to do what you suggest?

[dilipandey]

Okay...
Initially you asked how to force cell entry
and you got a code which has ".Range("D8:D22,D24:D37,D39:D46,D49:D58,D60:D82,D85:D121,D124:D155"))"

now you want to leave some cells blank so that other users can do cell entry which I understood from your below statement :-

How do I save the file with the cells blank so others have to complete the cells?

and hence I am saying

you can take out those cells from the range you specified in the above code.

for example, if you want some other user to do entry in D39:D46 then remove this from above code.

Regards,
DILIPandey
<click on below * if this helps>

[Patnaik]

@3earth

HI

as per my understand please change the below line

If WorksheetFunction.CountA(Worksheets("Schools to Watch").Range("D8:D22,D24:D37,D39:D46,D49:D58,D60:D82,D85:D121,D124:D155")) < 139 Then

Changed line

If WorksheetFunction.CountA(Worksheets("Schools to Watch").Range("D8:D22,D24:D37,D39:D46,D49:D58,D60:D82,D85:D121,D124:D155")) < 123 Then

Hope it is solved your problem other wise please inform

Thanks

SP

[3earth]

@ DILIPandey
I understand what you mean now. But I would like every mandatory cell to stay mandatory for other users not me as the user.
Thanks

[3earth]

@Patnaik
The new line did not allow me to save the file with blank mandatory cells.

[dilipandey]

I understand what you mean now. But I would like every mandatory cell to stay mandatory for other users not me as the user.

Not sure about this.. how workbook will understand that which user must fill what cells / ranges?
Either you provide this criteria ELSE workbook can simply check if a user has filled in xyz ranges before he can save or close the workbook.

Regards,
DILIPandey
<click on below * if this helps>

[Patnaik]

@Patnaik
The new line did not allow me to save the file with blank mandatory cells.

With the above mention code there is no link about users and Admin

and as per my understanding i think you want to restrict the workbook from save when there is no data in the mention ranges if it is correct then the code is working fine for me

if you want some different type then please give us some more details and it is better to upload a sample

Thanks

SP

[dilipandey]

I agree with Patnaik



Regards,
DILIPandey
<click on below * if this helps>