Ok this is a little strange...

If you put a list validation (like 'Y/N' from another range) on a cell, then
using the macro recorder record the following actions:
1) copy
2) select destination range
3) PasteSpecial (choose Validation as the paste option)

stop recording.

You get code like this:
Selection.Copy
Range("F3:F8").Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Now, when you try to run this with 'Option Explicit' there is an error
'variable not defined on the 'xlDatValidation' ! if you try to run it without
Option Explicit, the PasteSpecial method fails...

Not only that, if you search the Object Model (F2) for 'xlDataValidation' it
doesn't exist!

Of course, xlPasteAll works, but it is strange that the macro recorder can
find 'xlDatValidation' but the VBA Compiler can't and neither is it in the
Object Model!

Lovely !

So I say this is a bug - and how do we get it fixed (or is it alright in
XP/2003) ?

thanks

Philip

I found an answer (you have to use 6 for xlDataValidation)

Selection.PasteSpecial Paste:=6

still, strange that even though the macro recorder can find the enumerated
value, it's not available in the object model and VBA Compiler can't find it
either!

Philip

"Philip" wrote:

Ok this is a little strange...

If you put a list validation (like 'Y/N' from another range) on a cell, then
using the macro recorder record the following actions:
1) copy
2) select destination range
3) PasteSpecial (choose Validation as the paste option)

stop recording.

You get code like this:
Selection.Copy
Range("F3:F8").Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Now, when you try to run this with 'Option Explicit' there is an error
'variable not defined on the 'xlDatValidation' ! if you try to run it without
Option Explicit, the PasteSpecial method fails...

Not only that, if you search the Object Model (F2) for 'xlDataValidation' it
doesn't exist!

Of course, xlPasteAll works, but it is strange that the macro recorder can
find 'xlDatValidation' but the VBA Compiler can't and neither is it in the
Object Model!

Lovely !

So I say this is a bug - and how do we get it fixed (or is it alright in
XP/2003) ?

thanks

Philip

xlXP records it (properly) as xlPasteValidation.

check Object browser for Enum Excel.XlPasteType



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Philip wrote :

I found an answer (you have to use 6 for xlDataValidation)

Selection.PasteSpecial Paste:=6

still, strange that even though the macro recorder can find the
enumerated value, it's not available in the object model and VBA
Compiler can't find it either!

Philip

"Philip" wrote:

Ok this is a little strange...

If you put a list validation (like 'Y/N' from another range) on a
cell, then using the macro recorder record the following actions:
1) copy
2) select destination range
3) PasteSpecial (choose Validation as the paste option)

stop recording.

You get code like this:
Selection.Copy
Range("F3:F8").Select
Selection.PasteSpecial Paste:=xlDataValidation,
Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False

Now, when you try to run this with 'Option Explicit' there is an
error 'variable not defined on the 'xlDatValidation' ! if you try
to run it without Option Explicit, the PasteSpecial method fails...

Not only that, if you search the Object Model (F2) for
'xlDataValidation' it doesn't exist!

Of course, xlPasteAll works, but it is strange that the macro
recorder can find 'xlDatValidation' but the VBA Compiler can't and
neither is it in the Object Model!

Lovely !

So I say this is a bug - and how do we get it fixed (or is it
alright in XP/2003) ?

thanks

Philip

I did - it is not there in Excel 2000 ... like I said in my original
post...neither xlPasteValidation nor xlDataValidation

"keepITcool" wrote:


xlXP records it (properly) as xlPasteValidation.

check Object browser for Enum Excel.XlPasteType



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Philip wrote :

I found an answer (you have to use 6 for xlDataValidation)

Selection.PasteSpecial Paste:=6

still, strange that even though the macro recorder can find the
enumerated value, it's not available in the object model and VBA
Compiler can't find it either!

Philip

"Philip" wrote:

Ok this is a little strange...

If you put a list validation (like 'Y/N' from another range) on a
cell, then using the macro recorder record the following actions:
1) copy
2) select destination range
3) PasteSpecial (choose Validation as the paste option)

stop recording.

You get code like this:
Selection.Copy
Range("F3:F8").Select
Selection.PasteSpecial Paste:=xlDataValidation,
Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False

Now, when you try to run this with 'Option Explicit' there is an
error 'variable not defined on the 'xlDatValidation' ! if you try
to run it without Option Explicit, the PasteSpecial method fails...

Not only that, if you search the Object Model (F2) for
'xlDataValidation' it doesn't exist!

Of course, xlPasteAll works, but it is strange that the macro
recorder can find 'xlDatValidation' but the VBA Compiler can't and
neither is it in the Object Model!

Lovely !

So I say this is a bug - and how do we get it fixed (or is it
alright in XP/2003) ?

thanks

Philip

xl2000 was missing the columnwidth constant for paste special as well (
value: 8) as an example. So yes, I am sure it is a bug. the macro
recorder doesn't really record "dynamically". It is furnished fixed
information on what to record for each action. So apparently the recorder
people were led to believe the constant would be defined, but the constant
people never got around to implementing it would be my take.

--
Regards,
Tom Ogilvy

"Philip" wrote in message
...
I did - it is not there in Excel 2000 ... like I said in my original
post...neither xlPasteValidation nor xlDataValidation

"keepITcool" wrote:


xlXP records it (properly) as xlPasteValidation.

check Object browser for Enum Excel.XlPasteType



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Philip wrote :

I found an answer (you have to use 6 for xlDataValidation)

Selection.PasteSpecial Paste:=6

still, strange that even though the macro recorder can find the
enumerated value, it's not available in the object model and VBA
Compiler can't find it either!

Philip

"Philip" wrote:

Ok this is a little strange...

If you put a list validation (like 'Y/N' from another range) on a
cell, then using the macro recorder record the following actions:
1) copy
2) select destination range
3) PasteSpecial (choose Validation as the paste option)

stop recording.

You get code like this:
Selection.Copy
Range("F3:F8").Select
Selection.PasteSpecial Paste:=xlDataValidation,
Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False

Now, when you try to run this with 'Option Explicit' there is an
error 'variable not defined on the 'xlDatValidation' ! if you try
to run it without Option Explicit, the PasteSpecial method fails...

Not only that, if you search the Object Model (F2) for
'xlDataValidation' it doesn't exist!

Of course, xlPasteAll works, but it is strange that the macro
recorder can find 'xlDatValidation' but the VBA Compiler can't and
neither is it in the Object Model!

Lovely !

So I say this is a bug - and how do we get it fixed (or is it
alright in XP/2003) ?

thanks

Philip