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
– Foxfire And Burns And Burns, Commented Jan 29, 2018 at 12:23
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
– DarkRiot43, Commented Jan 29, 2018 at 14:09

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

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

DarkRiot43 · Accepted Answer · 2018-01-29 19:18:13Z

ANSWERING MY OWN Q: (Sorry if this isn't correct, thought it'd be good to let others know in the event of running into a similar issue).

After a bunch of research, and posting all over the place. It was determined that it was a NAME not a RANGE.

I had to re-write some code and change stuff out to make it work using arrays and split functions to iterate through the array with a for loop.

With respect to the code I'd posted, here it is fixed, but I only kept the stuff where there was a change for the declaration section.

Dim regOffice As Variant 'Range which will contain all the regional offices Dim hearingLoc As Variant 'Range which will contain the hearingLoc specific to the Reg Office regOffice = Evaluate("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:=Evaluate("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:=Evaluate("TESTMEMBERS") .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = False End With

Collectives™ on Stack Overflow

Data Validation using named Ranges containing only strings, no addresses

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related