Stop Input into Worksheet Based on Yes No Response

**fmluder93** · 03-04-2009, 11:21 PM

I am trying to work out how to stop input into a document if a certain response is selected from a form in excel 2007.

Basically, i need some programming so if a question is answered a certain way - the user cannot continue completing the form as it becomes "locked" and an error box comes up saying this is the case - and what action needs to occur. As the list is a data validation list - i cant just separate it out - so that if one response is chosen over another then the error message appears.

I have done searches in google - but have had no luck at all ...... !!!!!!

For example the question might be "Will you use contractors". If the answer is Yes, the cell turns red and an auto response fills the cell (VBA has already been written into the document for this to occur). What i need additionally is an error box to come up saying "This audit cannot continue as a Minor Plan needs to be completed" and the rest of the questions responses say something to the same effect.

I have attached an excel document outlining what it is im needing help on.

**royUK** · 03-05-2009, 04:01 AM

If you don't want the yes response, simply remove it from the validation list

**fmluder93** · 03-05-2009, 06:03 PM

I wish !!! And I wish it were that simple ... I need the yes in there as i need to know if someone picks it ... if they do - then what they need to do with the document will change !!! It will only affect about 3 of the questions in the whole document ...... So yes needs to be in all questions (as some yeses are what we want !!!!)

Its basically to make sure we have the right processes in place and if they tick yes to certain responses then people arent following the process - and by locking the document and having the error box - this will not enable them to keep going in the document ...

But thanks heaps for your input !!!!

**royUK** · 03-06-2009, 05:11 AM

The cell colour change is triggered by Conditional Formatting not VBA. To add a message you need to use the worksheet change event.

Please Login or Register  to view this content.

Copy the code
Select the worksheet in which you want he code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

**fmluder93** · 03-08-2009, 10:33 PM

This worked fantastically !!!!!

Thank you thank you thank you .... Muchly appreciated ......

I'll have a go at writing some coding around locking the document once this message box comes up !!!!!!! Hopefully i wont need to come back !!!!

BIG Cheers A

**royUK** · 03-09-2009, 03:38 AM

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save