VBA Validation using two worksheets

[gototcm]

Background: I want to validate a range of cells A1-A10 on sheet1. The valid values come from Sheet2, cells C1-C8 which contains the values 1-8 (C1=1, C2=2, etc.) The code runs without errors but the dropdown values in A1-A10 are blank and not the allowable choices 1-8. What is wrong with the following code?

[code]

Sub test()
Dim rng As Range
Dim range1 As Range

Set rng = Sheets("sheet1").Range("A1:A10")
Set range1 = Sheets("sheet2").Range("C1:C8")

With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & range1.Address
End With

end Sub
{/code]

Thanks for any suggestions.

[bakerman2]

Make the range on sheet 2 a named range and then use

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

[Greg M]

Hi there,

Please Login or Register  to view this content.

will return a value of $C$1:$C$8, but you need to include the name of the worksheet also, so try:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[gototcm]

Greg,

Thanks for your very helpful suggestion. That did the trick.

This 'Parent' thing is very confusing to me (the syntax) so if you have any suggestions on a good reference I would appreciate it.
I think I am moving from the trivial VBA area to the intermediate or borderline more advanced.

[gototcm]

Excellent. Thank you for the timely response and solution.

[Greg M]

Hi again.

Many thanks for your feedback.

You're welcome - glad I was able to help.

As there is a whole hierarchy of Objects in VBA, the Parent of an Object is the Object immediately above it in the chain.

For example, the Parent of a Range Object is a Worksheet; the Parent of a Worksheet Object is a Workbook; the Parent of a Workbook Object is the Excel Application. As a consequence, Range("A1").Parent.Parent.Parent.Name will return "Microsoft Excel".

Hope this helps,

Regards,

Greg M

[gototcm]

Got it. Very good and lucid explanation.

Thanks again.