Refresh a dynamically created listbox object on a dynamically created userform

[mhwalmsley]

Hi all

First time poster so please forgive any toe treading that happens.

The attached sheet shows a snippet of the worksheet I'm using to create a userform on the fly.

The code creates the objects on the form according to the Type in Row 1. If the Type is a listbox, the values are populated using the contents of a named range in row 2. This range is dynamic and will grow every time an Org_List entry is added.
The code also creates a set of events dynamically so I can trap a MyListBox_Change() event for the "Referred From" listbox.

This allows me to populate the Named Range value for "RFCOtoKpoc", "Referred_From_Calling_Org_to_Key_point_of_contact". This works fine, populating the Value with Contacts_Org2 when Org2 is Selected in the "Referred From" list box.

On entry to the "Key point of contact" listbox, I'm able to get the "Contacts_Org2" value from RFCOtoKpoc, as indicated by the "LinksFrom" row. This is then set to the MyListBox.RowSource. All good so far. I've also used direct Range() references in case there's an issue with setting that property.

What I am unable to do is refresh the list in the "Key Point Of Contact" listbox to reflect the contents of the Contacts_Org2 list from that named range.

When in the procedure where I'm attempting the refresh, I can successfully get MyListBox.Name, MyListBox.RowSource, and any number of properties. Trying any listbox method such as MyListBox.Clear, fails with

Run-time error '-2147467259 (80004005)':

Unspecidied error

The code snippet is below.

I'm currently resorting to contemplating re-building the second list box or rebuilding the form entirely. I don't yet know how to remove the second list box and re-building the form completely seems very messy.

Any and all comments appreciated.

    MsgBox "The ListBox is " & MyListBox
'    MsgBox "The value is " & MyListBox.Text
'    MsgBox "The list source is " & MyListBox.RowSource
'    MsgBox "The object's Tag property contains " & MyListBox.Tag

    rval = Int(Left(MyListBox.Tag, 1))
    cval = Int(Right(MyListBox.Tag, 1))
'
    
    ' When rvsl = 4, we're storing the name of a list, e.g. the Org_Contacts list
    ' The list name being stored is the Named Range of the list we need to attach to a dependent list box, e.g. The Contacts list for the Org.
    ' thus making it dynamic
        
    If rval = 5 Then ' Using the MyList.Tag property set earlier to 5 if the LinksFrom row is not blank (the cval is set to the column for the object)
        
        MsgBox "The object's Tag property contains " & MyListBox.Tag
        MsgBox "This is where the linked list processing is set up."
        
        ' Set the Cell selected to point to the Value row, objNumber column)
        Cells((rval - 1), cval).Select
        
        ' Set the value to point to the name from row 5
        lookupFormula = "=" & Cells(rval, cval).Value
        
        ' Update the cell pointed at with the LinksToValue
        ActiveCell.FormulaR1C1 = lookupFormula
        
        ' Set the Object.RowSource to the LinksToValue
        ' Property Let MyListBox.RowSource = Cells(Int(Left(MyListBox.Tag, 1) - 1), Int(Right(MyListBox.Tag, 1))).value
        MyListBox.RowSource = Range("E4").Value
        
		' The code aboove successfully updates the RowSource with the list to use, whether set up by Range as here, or by reference to the address
		
		'This is one example of code that doesn't work
        MyListBox.Clear
        MyListBox.List = ActiveCell.FormulaR1C1