VBA Excel Validation From Named Range Error 1004

Question

I have a macro that adds validations on a change event. In name manager, I had a table that was "Table1" that I changed to "tblBrand". This line of code is supposed to create a list from that table, but it's throwing error 1004.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="=tblBrand"

Weirdly, I had used the name box previously, and named this table "shtblBrand" before, and this worked fine. But in name manager, I basically had each table named twice. So this table was in name manager as Table1 and shtblBrand. So to be efficient, I deleted the shtblBrand, and just renamed Table1 to tblBrand. That's when it stopped working and threw the error 1004.

Could you please share more of the code? What is the Object that .Add is called on? — z32a7ul
– z32a7ul, Commented Jan 31, 2023 at 13:37
What's happening when try Range("tblBrand").select? Does it selecting the range you need to process? — FaneDuru
– FaneDuru, Commented Jan 31, 2023 at 13:37
Range("tblBrand").select selects the range without the header. — Gary Nolan
– Gary Nolan, Commented Jan 31, 2023 at 13:39
This is the full code for that validation .With shPD.Cells(tr, Range("pdBrand").Column).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="=tblBrand" End With — Gary Nolan
– Gary Nolan, Commented Jan 31, 2023 at 13:39
This only proves that the range used in Formula is correct (the table DataBodyRange). What about Range("pdBrand")? — FaneDuru
– FaneDuru, Commented Jan 31, 2023 at 13:53

z32a7ul · Accepted Answer · 2023-01-31 14:36:11Z

1

~~You should not put an = sign into the formula, the corrected code is:~~ Actually, you should.

With shPD.Cells(tr, Range("pdBrand").Column).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="tblBrand"
End With

OK, I've experimented further. Somehow Tables are not normal Names. If you look at them in the Names window, they have a different icon, and they are not enumerated in ThisWorkbook.Names. And as it turned out, they are not allowed in this Formula1.

You should either create a new name with the same range, or create a new name, e.g. tblBrandIndirect that refers to the table =tblBrand (possible via VBA but easier through Names window), and use that name in Formula1:

With shPD.Cells(tr, Range("pdBrand").Column).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="tblBrandIndirect"
End With

This way you can use it both as a table and as a normal name, and do not need to update its address.

edited Jan 31, 2023 at 14:36

answered Jan 31, 2023 at 13:51

z32a7ul

3,8474 gold badges24 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary Nolan Over a year ago

Oh, wait. I tried and I no longer get the error, but it's just making that name "tblBrand" my only choice in the validation list. It's not bring in the table. list of 132 items that name represents

Collectives™ on Stack Overflow

VBA Excel Validation From Named Range Error 1004

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related