Creating a Validation List in VBA

Question

I can't work out why this isn't working. I have a subroutine which builds a validation list from data on another Excel worksheet. I have similar subroutines that work okay but not this one.

If I change

    ReDim CatsValidationList(catsArray.Count)

to

    ReDim CatsValidationList(100)

Or any number, then it works fine. I know catsArray is populated and there is also a figure for catsArray.Count. So what am I missing when it comes to resizing the array? Hopefully someone can help. Full code for the sub is here...

Sub all_cats()

'Set some variables
Dim category_list As Range
Dim catRng() As Variant
Dim catsArray As New Collection

'Empty
Range("D15:D1000").Clear
Range("F15:F1000").Clear

'Set range of data
Set category_list = Worksheets("All Cats").Range("B1", Worksheets("All Cats").Range("B10000").End(xlUp))
catRng = category_list

'Populate array with data
On Error Resume Next
For Each ct In catRng
catsArray.Add ct
Next

'Resize array
ReDim CatsValidationList(catsArray.Count)

'Populate array for validation list
For xx = 1 To UBound(CatsValidationList)
CatsValidationList(xx) = Worksheets("All Cats").Range("B" & xx).Value
Next xx

'Build validation list
With Range("D15").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlEqual, Formula1:=Join(CatsValidationList, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = False
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

With Public CatsValidationList() As Variant at the top

I've tried declaring it within the sub, I've tried assigning the number to a variable. But it's not happy.

Thanks in advance

It is part of a larger program I've written that compiles validation lists of matching keywords in a small keyword list from the main dataset. The sub for the matching keywords works fine and I'm trying to write the sub for all_cats the same way. It features a resized array... ReDim ValidationList1(stylecats_exact.Count) and a bit that looks for matching words and adds them to the array

If child_cat_exact > 0 Then
stylecats_exact.Add Trim(Worksheets("All Cats").Range(catlist_index & cat_item.Row).Value) & " [" & Worksheets("All Cats").Range(catid_index & cat_item.Row).Value & "]"
Else

which isn't required in the all_cats sub. But there's something missing somewhere because I can't populate the CatsValidationList with

ReDim CatsValidationList(catsArray.Count)

unless I put in a specific number

ReDim CatsValidationList(190)

which is no good because the full list can often change.

I'm puzzled...:(

Is the reason you are doing this because the Category list is dynamic and have more or less values each time? If so, you can get rid of VBA completely and use a Dynamic Named Range as your list value. — Scott Holtzman
– Scott Holtzman, Commented Jan 5, 2016 at 13:37
The problem seems to be with For xx = 1 To UBound(CatsValidationList) (and possibly others). The CatsValidationList is a zero-based array and you are redimming it and for ... nexting it using a 1 based count. Maybe just put Option Base 1 at the top of the module page. See Option Base Statement. — user4039065
– user4039065, Commented Jan 5, 2016 at 13:58
FYI, the limit on a validation list length when you use a comma delimited string is 255 characters. If you save and reopen the file with a longer list set, you'll get an error. You'd be better off using a range. As a general comment, if you use On Error Resume Next make sure you turn it off with On Error Goto 0 or similar as soon as possible. — Rory
– Rory, Commented Jan 5, 2016 at 14:00
What is the error message you receive when you try using ReDim CatsValidationList(catsArray.Count) ? — CodeJockey
– CodeJockey, Commented Jan 5, 2016 at 15:00
Thank you for all your comments and suggestions. After a bit of testing, I see it is in fact the validation list length as you mention, Rory. So I'll look into using a range instead. Thank you — user3683463
– user3683463, Commented Jan 5, 2016 at 15:01

user3683463 · Accepted Answer · 2016-01-05 16:02:14Z

As pointed out in the comments, the issue was with the validation list length rather than anything else. So I took the advice of using a range rather than build up an array to populate the list with. It now works fine. (Thanks Rory) For those who have come here looking for a similar solution to a similar issue, I amended the validation bit to include .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=" & category_list

With the variable category_list referring to the range from another worksheet called All Cats. category_list = "'All Cats'!B2:B" & cat_end_row

Full code for my sub is below for anyone else's reference and to put the above into context. I'm sure there are other ways of writing it that are cleaner and more efficient, but for now this works okay for me. Thanks StackOverflow

'Set some variables
Dim category_list As String
Dim catRng() As Variant
Dim catsArray As New Collection
Dim cat_end_row As Integer

'Empty
Range("D15:D1000").Clear
Range("F15:F1000").Clear

'Find end row
cat_end_row = Worksheets("All Cats").Range("B1000000").End(xlUp).Row

'Set range of data
category_list = "'All Cats'!B2:B" & cat_end_row

'Build validation list
With Range("D15").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=" & category_list
    .IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = False
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Collectives™ on Stack Overflow

Creating a Validation List in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related