force a user to fill a certain cell

[fanny.may]

Hello
I started a validation of an excel sheets, have some questions:
1. how can I force a user to fill a certain cell - say to introduce his name of some form number before he wants to print or before he wants to save?
2. Can I leave only a small part of a sheet existing making disappear all the rest? Lets say I need to validate a sheet and I decided to check during the validation every cell in it (not the risk managment approach), can I leave only a part of it an an excell sheet (like a word documents - which contains 1 or 5 or 20 pages - a final number of cells, let's say a rectangle A-G, 1-30

[ChemistB]

Starting with your second question. You can unlock only certain cells and not allow anyone without the password to enter values into others (or even to select others). You could also hide all unused rows and columns. With either technique, you would "Protect the worksheet"

The best way to achieve the first part is using VBA (programming code) to check to see if a cell is empty before printing and if it is, cancel printing and output a message to fill in the cell. Is that what you are looking to do? This will only work if Macros are enabled, however.

[fanny.may]

Dear Chemist B,
The worksheet protection in all different levels is learned and done. I wounder if I can make my sheet as a finite document, like a word document. You have mentioned hiding rows, can I leave 30 rows of my sheet and hide all the rest, then lock it?

Regarding the other question - I use those sheets for a simle calculations in a pharmaceutical field company, Validation of excel is a new thing that we implement, to be able to use excel. Validation a VBA code would be more complicated. Can I force user to fill in the cell without VBA? may be with some "if.." simple thing? Could you suggest something particular?
Thank you
Fanny

[ChemistB]

Q1. Yes, Click on the row number after your last row (i.e. 31) thus highlighting that row. Use CNTRL SHFT Down Arrow to select remaining rows. Right click and "Hide" The same can be done with columns using the Right Arrow.

Q2. You can use "Data Validation" but if the user pastes the value in rather than manually entering it, the data validation disappears. Let me know if you want that explained. For the VBA, you'll want to copy the below code into your workbook module and modify it for your data.

Please Login or Register  to view this content.

Change the worksheets name and the range to match your data.
To paste into workbook module
ALT + F11 to enter VBA Editor
You should see a folder like window on the left with your worksheets and a folder called "ThisWorkbook." Double click on "ThisWorkbook".
Paste the code into the white text window on the right. Close the VBA editor (no need to save, it will save with the workbook)
Did that help?