Assigning a combobox a named list in vba

Question

I'm trying to dynamically assign a list to every combo box based on the values of a specific combo box. The idea is that the user picks a category from the specific combobox and all other combo boxes grab the items from that category in the form of a named list.

So the structure is like

Category 1

Item 1 Item 2

And so on. I had this working on a fake set of names, but now that I'm using real named ranges, the code breaks. It is breaking on "For Each rng In ws.Range(str)" and stating that "method 'range' of object '_worksheet' failed.

This code works. Or worked. Then I changed ws to point to a different sheet of named ranges and now nothing works.

The value of CBOCategory is any value from a list of all named ranges, but it seems like Excel isn't seeing any of them! I tried to trigger even a listfill assignment instead of adding each item and got a similar error

    Private Sub CBOCategory_Change()
      'Populate dependent combo box with appropriate list items
      'according to selection in cboCategoryList.
      Dim rng As Range
      Dim ws As Worksheet
      Dim str, temp, cbName As String
      Dim counter As Integer
      Set ws = Worksheets("Item Master")


    Dim obj As OLEObject
    str = CBOCategory.Value

    For Each obj In ActiveSheet.OLEObjects
        If obj.Name = "CBOCategory" Then
            ' nothing
        Else
            temp = obj.Object.Value
            obj.Object.Value = ""
            For Each rng In ws.Range(str)
                obj.Object.AddItem rng.Value
            Next rng
            obj.Object.Value = temp
        End If
        'MsgBox ("updated!")
    Next obj



    End Sub

Although I doubt it is causing the error, you really should declare str as a String rather than letting it default to Variant, i.e. Dim str As String, temp As String, cbName As String. — YowE3K
To add to @YowE3K's comment, it's a little unintuitive but Dim str, temp, cbName as String does not declare all those as a String, it only declares cbName as a String, the others will be Variant which is the default when a type isn't given. — BruceWayne
What is the scope of your named ranges? If they are only scoped to a specific worksheet, ws will have to be that worksheet — CallumDA
All of the named ranges live in ws / The sheet named "Item Master". The named ranges are set up to go the entire height of a column (so that they can expand when added to) using the following as a format =OFFSET(Item Master!$AR$2,0,0,COUNTA(Item Master!$AR:$AR)-1,1) Where in this case AR is the column the named range is located. — user2533406
Tried For Each rng In ws.Range(str).Cells? What you get in immediate window for Debug.Print str just before that For loop? Ensure it's not "". — PatricK

user2533406 user2533406 · Accepted Answer · 2017-03-23T16:57:21

The code works fine. The root cause of the issue is that the named ranges were being dynamically set by a formula. The formulas were not calculating properly when the code ran, so vba could not use a dynamically set named range to find another, also dynamically set named range.

The solution is to explicitly set the named ranges. Then the code works fine.

Assigning a combobox a named list in vba

Categories

Category 1

1 Answers