Data Validation using named Ranges containing only strings, no addresses

Question

I'm creating a workbook/tracker for a group of not so comfortable excel users, hence I've named all the ranges as seen in the picture linked below (Sorry I don't have enough rep to put it in my post). Their scope is workbook.

I've given them all a name, i.e. "TEST" & "TESTMEMBERS" and have filled their content manually in the UI (Ctrl F3) so that the users may amend the named ranges easily should they need to. Their contents are simply a string instead of referring to a range of cells somewhere in the workbook.

For example:

"TESTMEMBERS" has the following data (aka refers to): ="Member1, Member2"

My question is how would I go about getting the data from the named range to be used for data validation of a cell using VBA?

I get the following error at the line at Set regOffice:

Run-time error '1004' Method 'Range' of object '_Worksheet' failed

Below is the part of my code which is having problems. It is worth noting that the sub is the Worksheet_Change. Please let me know if you need more. I'll happily post it. I've included the DIM section to ensure I've set everything up as it should.



Dim regOffice As Range                   'Range which will contain all the regional offices
Dim hearingLoc As Range                 'Range which will contain the hearingLoc specific to the Reg Office
Dim wsTracker As Worksheet              'Worksheet var which will ref the Tracker Worksheet
Dim colCounter As Long                  'Counter
Dim i As Long


Dim hearingDates As Range
Dim day As Range
Dim tableHeaders As Range
Dim header As Range
Dim colLoc As Long
Dim colOpened As Long
Dim colScheduled As Long
Dim colRecording As Long
Dim colAdjourned As Long
Dim foundLocation As Range

Set wsTracker = Application.ActiveSheet


            Set regOffice = Range("TEST") 'The purpose here is to use regOffice range later and iterate through it's values.

            'Office Location Validation
            With Range("B24", "B100").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="=TEST"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = False
            End With

            'Member Name Validation
            With Range("D24", "D100").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="=TESTMEMBERS"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = False
            End With

Picture of how the Named Range is set-up

Ranges must be referenced to cells. You created a name which refers to no cells, and when you try to assign it to RegOffice, you get the error. That explains why you get the error. Something like ThisWorkbook.Names("TEST").RefersTo maybe can help you to access the info you need, but I would advice to put all members in cells, and then create the name "TEST" referenced to those cells. Thay way, your code should work — Foxfire And Burns And Burns
Thank Foxfire, You're right, I created a name. I had no idea those existed. I've had to refactor my code using Evaluate("TEST") to put the string into regOffice. From there I split("regOffice", ", ") which split the values into single locations. Similarly, for the Formula1:= part of my code, I had to change that up a bit, some syntax errors were present. The new code is just uses the Evaluate function again: .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:=Evaluate("TEST") Thanks for your help. I've learned about a new vba object :) — DarkRiot43

Foxfire And Burns And Burns Foxfire And Burns And Burns · Accepted Answer · 2018-01-29T14:30:13

Ranges must be referenced to cells. You created a name which refers to no cells, and when you try to assign it to RegOffice, you get the error. That explains why you get the error. Something like

 ThisWorkbook.Names("TEST").RefersTo

maybe can help you to access the info you need, but I would advice to put all members in cells, and then create the name "TEST" referenced to those cells. Thay way, your code should work

Data Validation using named Ranges containing only strings, no addresses

2 Answers