[nikeshtnt]

I have created a Reimbursement claim form. In this form there are some information has to be filled and based on that some formula is there. I want that if some one fills this then he/she should not leave any required information blank.

Informations are like, Emp Code, Name, Dates etc.

When employee filles this form they never mention Employee code, which is mandatory.

I want to put a validation in that excel file so that they get a prompt massage while saving excel file before filling all required details (all yellow cells in the attached xls file) . They can save only after filling all details.

Can I get some macro for this.

[JBeaucaire]

Is this form a template they open (.xlt), or an .xls they open?

[JBeaucaire]

Here you go. I added a script that runs whenever you try to SAVE the document. I also changed the form to an .xlt so that when it is opened it is not the original document, is a new document based on the template.

If you want to see what I did, press ALT-F11 to open the VB Editor, then double click on ThisWorkbook on the left panel. It will open the script and you can look it over.

Cheers.

[JBeaucaire]

Also, there are some typos I couldn't correct since you had the sheet protected:

Assignement = Assignment
entitelment = entitlement
mendetory = mandatory

Also, while the sheet is unprotected select all the cells that AREN'T yellow, open the FORMAT CELLS > PROTECTION
Lock the cells you don't want edited

When you protect the document, UNCHECK the "select locked cells" option so the users only have to cursor through the yellow cells.

Nifty.

[nikeshtnt]

Thanks a ton!

It is a template so and as it is opening in excel is fine.

Just a small question... Is there any do & don't in this file to run macro properly?

[JBeaucaire]

No, the macro runs ITSELF every time you try to save it. Pretty cool.

But, the script that is checking the yellow cells will not update itself if you ever redesign or update the content of this template. If you move a cell or add another one, you will have to MANUALLY edit the script to accommodate.

Also, as you may have already have figured out, the cells are blank when you open the template, but they cannot be blank to save it. This includes saving the template. So, you might ask, "How do I save the template in the first place if the template needs the cells to be blank but there is a script running that won't let me save it if the cells are blank?"

The trick is to open template, make your edits/updates, then turn on the CONTROL Toolbar and click the "Design Mode" button. With the Design Mode clicked, you can save the template and the script won't run.

Since I couldn't make the typo changes I noted above, this would be a good test for you to see if you can update the template.