-
Nov 9th, 2022, 04:30 AM
#1
Thread Starter
New Member
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
-
Nov 9th, 2022, 04:41 AM
#2
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|