detect validation list

[Guest]

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.

 

[Jim Rech]

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

--
Jim
|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.

 

[Guest]

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]

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
| 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
| > | > |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.
| >
| >
| >