Results 1 to 2 of 2

Thread: Excel VBA macro to set validation list based on other cell

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    2

    Excel VBA macro to set validation list based on other cell

    Hi,

    I have an XL sheet where I have a simple dropdown with list with 2 entries (Manufacturing and Health). For each of these I have separate named ranges that include the locations that we work with in those industries. Each of these named ranges are given a name according to the first dropdown - so e.g. Manufacturing_locations and Health_locations.

    What I want to do is when the first dropdown is selected, to populate a second dropdown with values from those named ranges - so that this second dropdown is dynamically populated.

    This is the sub that I've got but each time it gets to the Validation.Add call I get the error method range of object _worksheet failed. Thoughts?

    -Dave

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
        ' Set defaults based on Solution choice
        If Not Intersect(Target, Range("ChooseIndustry")) Is Nothing Then
            Dim name
                 
            name = Range("B2").Value & "_locations"
            
            Range("Customer Input!B4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Range(name).Address
     
        End If
        
    
    End Sub

  2. #2

    Thread Starter
    New Member
    Join Date
    Nov 2022
    Posts
    2

    Re: Excel VBA macro to set validation list based on other cell

    FYI I've just added the line

    var = Range(name).Address

    after where I create name = Range("B2").Value & "_locations" and it fails on that call reporting the same error so this is the culprit. It is as if it can't get the address of the range - BUT - the named range is global (i.e. workbook scope) and if I actually replace the "name" with the direct reference to the cells where the list data is it also fails (Lookups!$C$3:$C$6)

    It's deffo to do with referencing a range on another worksheet as if I reference a range on the same worksheet (e.g. removing "Lookups!" reference) it works fine - does Range.address function not work across worksheets? Odd if not.
    Last edited by baldgeezer; Nov 9th, 2022 at 04:54 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width