Cell validation only if particular cell not blank

[JohnOB]

have a spreadsheet I am using as a form, with validation to allow in cell drop downs. We would like the form to be setup in a way that the users could not fill it out in case the previous section had been completed.

Is it possible that the cell could be blank, or locked, until a particular cell had been completed, and once this cell was completed the cell would then show the list from the validation.

Thanks

"Stupid isn't not knowing the answer, it's not asking the question

 

[SkipVought]

Hi,

I'd use a combination of sheet protection and the Worksheet Selection Change event.

With Sheet Protection, you have to unlock the cells that the user can select and change. In the Workbook Open event, you select the first cell that the user must enter, and record the address. When the user changes the selection, the selection change event, checks the previously recorded address for an appropriate entry. If not satisfied, forces the selection to the previous address and posts a message if required.

Richt-click the sheet tab and select View Code. This exposes the worksheet code sheet. Above the code window are two drop downs. Select the Object drop down and select Worksheet. Select the Procedure drop down and select Worksheet_SelectionChange. This is the event procedure to trap selections on the sheet.

In the Project Explorer (ctr+R) you will see the workvook objects. Select ThisWorkbook, then the Workbook in the Object drop down and Open in the Procedure drop down. Thi is where you will set the first selection and record the address. I'd store the address on a hidden sheet.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JohnOB]

Thanks Skip

This sounds a little beyond me so what I decided to do instead was use conditional formatting to set the text colour to white, if the previous cell was blank. So allthough the user could see the drop down, then the made a selection it did not appear, and was not visible on print outs etc. I locked the sheet and passworded it so hopefully this should do the job.

Bit of a round about way of solving the problem and probably not the best method but it seems to produce an acceptable result

"Stupid isn't not knowing the answer, it's not asking the question

 

[combo]

Having in mind all limitations of validation (data can be pasted and cleared, no longer valid data stay in cell) you can use dynamic reference in the list, for instance:
=IF($A$1<>"",$A$2:$A$10,$A$11)
where A11 is an empty cell. Clear 'Ignore blanks' option.


combo