Validating contents of a cell on input based on the contents of another cell

Hi all,

Currently working on some code to do something that's a fair bit ahead of anything I've done in VBA before, so I'm turning to you fine people for assistance!

What I'm trying to work on is this:

In Column G, the user can select one of 7 options to provide something to an employee. Based on the option selected they then need to go into column S suggest the length of provision, and then column T to suggest the value of the provision.

Each of the 7 options has different valid lengths and values.

I want to set up a macro so that when they enter something in either S or T it checks that what they have entered falls within the range for that option, and if not pops up an error until they suggest something appropriate.

Any suggestions? I've had a really good google for this, and can't find anything that might be appropriate.

Thanks in advance for any help!

Originally Posted by TMShucks

Rather than make it trial and error, what don't you set up dependent drop down boxes. You'd probably still need code to clear the dependent values but the code would be much simpler. Look at the Contextures web site for advice.


Regards, TMS

The issue is that the value is not from a list, but can be anything between X and Y in both cases, otherwise I would have done data validation with some indirects in excel.

With sincere apologies to anyone who writes VBA for a living (as the following will make your eyes bleed.

I managed to get it working like so -

Please Login or Register  to view this content.

I'm pretty sure that there's going to be a better way of doing this though!

The above is dependent on them hitting enter to put the value in. I do know the odd person who uses arrow keys which will make all of the above fall over :/ If someone can suggest a way around that it would be appreciated!

Above still doesnt quite work :/

Suspect its a problem with my "If Not And stuff"...

Flipped them to "If/Or" and changed the direction of the inequalities and it now works. The only problem now is that once it clears the contents, the value of the cell is invalid and it throws it into an infinite loop. Oh dear.

Originally Posted by Winon

Hello Saigonet,

Your WorkBook contains no formulas or dropdowns, making it very difficult to try and understand what you are after.

Its explained in the text in the first post - Basically just only allowing certain values in cells based on the contents of another cell. Because they are not discrete values, I couldn't use a drop down to do it.

I got there in the end though, albeit it looks horrible. Just had to turn off and on application events