Validation cell in UDF?

  • Thread starter Thread starter Greg Allen
  • Start date Start date
G

Greg Allen

I want to use a user-defined function as part of a cell validation routine.
Is there a way from within the function to determine what cell is
being validated?

Does anyone have examples of something similar?

Thanks,

-- Greg
 
Greg,

Try

Application.Caller.Address

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
That does work.

However, what I want to do is a little different.

I want to use a list validation type. In the range of cells that defines the
list, I want to put a formula that will do some validation. In that formula,
I need to know the address of the cell being validated.

Using Application.Caller.Address in this case gives me the cell that
is part of the validation range.

Is there a way to get the cell being validated?

Thanks,

-- Greg
 
Just use the reference of the activecell when you add the DV, Excel will
update to each appropriately.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Well, I am close to what I want. Thanks for the help.

I created a named range for my validation, A1:A7, (called MyValidate) that
looks like this:

Blocked
In Progress
Listed
N/A
Not Started
Submitted
=CheckInteger()

The CheckInteger function simply check to see if the number in the current
active cell
is a whole number.

I set validation for a cell, choose the type of list, and set the source to
be "=MyValidate"

This almost works.

However, it appears that CheckInteger is not being called when the
validation is occuring.
What happens is that CheckInteger is evaluated when I enter the formula, and
a 0 appears there.
The 0 appears in my dropdown (with all my other options) when I edit the
cell, and if I enter a 0
it works. But if I enter any other number, it fails.

Can I somehow force validation to "run" this formula each time validation
occurs? Or can
I get this to happen some other way?

Thanks,

-- Greg
 
Greg,

I wouldn't do it like that. Before I make a suggestion, can I check my
understanding. Do you want the list to include the result of that
CheckInteger function, or something else? I assume it just returns true or
False?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
No, what I want is to validate that the value in the cell is either:

1) one of the items in the list (and I would like the user to see the
dropdown), or
2) a whole number

This is really a combination of two existing validation types, the "whole
number"
validation and the "list" validation.

-- Greg
 
Bob,

Did you still have a suggestion after learning what I am looking for?

Thanks,

-- Greg
 
Back
Top