Kamis, 22 September 2011

Re: [ExcelVBA] Data validation question

 

Easiest solution, I reckon, is simply to highlight the cell in red using
conditional formatting.

Otherwise, enhance the formula to check for an invalid result, and show an
appropriate message in the cell.

You could write a bit of VBA that would check the value of the cell and pop
up a message, but the above two approaches are probably adequate.

Regards, Dave S

----- Original Message -----
From: "phil4england" <philip.cosgriff@virgin.net>
To: <ExcelVBA@yahoogroups.com>
Sent: Thursday, September 22, 2011 6:48 PM
Subject: [ExcelVBA] Data validation question

Hi guys

I am trying to apply data validation to an output cell (a cell containing a
formula) rather than an input cell, as is normally the case. Excel (2007)
allows me to apply a condition (decimal,between,0.1-60 [yrs]) but the only
way I can get the error `highlighted' is by selecting "circle invalid data"
(which draws an oval shape around the cell in question). The user will be
unaware of this (!), so how do I make the warning message pop up when one of
the associated input cells causes the result to be out of range?

For example
A1: 01/02/1956 ..UK date format, dd/mm/yyyy
A2: 19/09/2011
A3: =(A2-A1)/365 .. to give the answer in years

Then I want to apply the above validation to A3. If answer (age) is not in
range 0.1-60, display error message (warning type) when a date cell (either)
is entered. Could test the date cells themselves, but that makes life more
complicated.

For example, if date in A1 (which is date of birth) is entered as
01/02/1947, this should immediately produce the error message (ref: Error
Alert tab within the Data Validation dialogue box). I'm sure I could do
something with conditional formatting, but the indications are that it can
be done using validation, as Excel 'knows' that the result data (in A3) is
invalid in the case..

Would be good if the solution (if there is one) also applied to Excel 2010.

Thanks a lot, Phil

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1410 / Virus Database: 1520/3911 - Release Date: 09/21/11

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar