+ Reply to Thread
Results 1 to 7 of 7

VBA Validation using two worksheets

  1. #1
    Registered User
    Join Date
    12-29-2011
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2003
    Posts
    31

    VBA Validation using two worksheets

    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.

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    5,901

    Re: VBA Validation using two worksheets

    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.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    Posts
    2,697

    Re: VBA Validation using two worksheets

    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

  4. #4
    Registered User
    Join Date
    12-29-2011
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: VBA Validation using two worksheets

    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.

  5. #5
    Registered User
    Join Date
    12-29-2011
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: VBA Validation using two worksheets

    Excellent. Thank you for the timely response and solution.

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    Posts
    2,697

    Re: VBA Validation using two worksheets

    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

  7. #7
    Registered User
    Join Date
    12-29-2011
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: VBA Validation using two worksheets

    Got it. Very good and lucid explanation.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1