detect validation list
 

I have a form that contains a listbox of items. When an item is
double-clicked a row of values is entered at the next available row in the
worksheet. Some of the cells in the newly appended row contain validation
lists. I have various types of lists and in some cases when an item is
selected from a list I need to dynamically create a corresponding validation
list in the cell to the right of it. I can do this bit. The problem I have is
that I need to detect whether the current cell contains a validation list as
not all cells will be validation lists. The code will be in the
Worksheet_Change event. I have looked at the Target argument and
Target.Validation, but cannot find a property to give me the info I need. Any
help would be greatly appreciated.

detect validation list
 

If the cell's validation type is 3 it has a validation list. And the list's
address is returned with Validation.Formula1.

--
Jim
"svb" wrote in message
...
|I have a form that contains a listbox of items. When an item is
| double-clicked a row of values is entered at the next available row in the
| worksheet. Some of the cells in the newly appended row contain validation
| lists. I have various types of lists and in some cases when an item is
| selected from a list I need to dynamically create a corresponding
validation
| list in the cell to the right of it. I can do this bit. The problem I have
is
| that I need to detect whether the current cell contains a validation list
as
| not all cells will be validation lists. The code will be in the
| Worksheet_Change event. I have looked at the Target argument and
| Target.Validation, but cannot find a property to give me the info I need.
Any
| help would be greatly appreciated.

detect validation list
 

Thanks Jim, that will do nicely. The only point I have is that If I do
something like,

If Target.Validation.Type = 3 Then
'// and do some stuff
End If

and the cell I change does not have a validation, which will happen as not
all cells have lists, then I receive an application error. This is not a
problem though as I can trap the error and continue.

"Jim Rech" wrote:

If the cell's validation type is 3 it has a validation list. And the list's
address is returned with Validation.Formula1.

--
Jim
"svb" wrote in message
...
|I have a form that contains a listbox of items. When an item is
| double-clicked a row of values is entered at the next available row in the
| worksheet. Some of the cells in the newly appended row contain validation
| lists. I have various types of lists and in some cases when an item is
| selected from a list I need to dynamically create a corresponding
validation
| list in the cell to the right of it. I can do this bit. The problem I have
is
| that I need to detect whether the current cell contains a validation list
as
| not all cells will be validation lists. The code will be in the
| Worksheet_Change event. I have looked at the Target argument and
| Target.Validation, but cannot find a property to give me the info I need.
Any
| help would be greatly appreciated.

detect validation list
 

then I receive an application error

Right. Kind of ugly for Excel to blow on things like this but, as you say,
you can trap it.

--
Jim
"svb" wrote in message
...
| Thanks Jim, that will do nicely. The only point I have is that If I do
| something like,
|
| If Target.Validation.Type = 3 Then
| '// and do some stuff
| End If
|
| and the cell I change does not have a validation, which will happen as not
| all cells have lists, then I receive an application error. This is not a
| problem though as I can trap the error and continue.
|
| "Jim Rech" wrote:
|
| If the cell's validation type is 3 it has a validation list. And the
list's
| address is returned with Validation.Formula1.
|
| --
| Jim
| "svb" wrote in message
| ...
| |I have a form that contains a listbox of items. When an item is
| | double-clicked a row of values is entered at the next available row in
the
| | worksheet. Some of the cells in the newly appended row contain
validation
| | lists. I have various types of lists and in some cases when an item is
| | selected from a list I need to dynamically create a corresponding
| validation
| | list in the cell to the right of it. I can do this bit. The problem I
have
| is
| | that I need to detect whether the current cell contains a validation
list
| as
| | not all cells will be validation lists. The code will be in the
| | Worksheet_Change event. I have looked at the Target argument and
| | Target.Validation, but cannot find a property to give me the info I
need.
| Any
| | help would be greatly appreciated.
|
|
|