populate control from named range stored in addin

[MrBlackd]

I have inside an add-in a Userform that has a ComboBox and a ListBox.
Until now both controls have been populated using AddItem method and also the ListBox has been dependent on the item selected in the ComboBox.
I have created a Data sheet in the add-in, typed there all the items that I added with AddItem method and I created several named ranges so as to populate the controls using the named ranges that I created and RowSource method.
The problem is that if the workbook property isaddin is true I get runtime error 380 but if I set isaddin to false the userform controls populate properly with no error code.
I have been searching in this and other forums for a solution and I have been googling for half day to find a solution to this but with no luck.
Any suggestion to fix the issue?
I have tried several things I found to better refer to the named ranges in the addin but with no luck...
Ultimately I can always go back to Additem method as I did before but I can't believe that there is no solution to this issue.

Thanks in advance for any reply.

[Admin]

Could you please upload the workbook ?

[MrBlackd]

I have attached a similar far simpler file that is reproducing the issue mentioned above.

In procedure UserForm_Initialize of Form UserForm1 I get
Error 380 (Could not set the RowSource property. Invalid property value. )

The problem is here:

ComboBox1.RowSource = ThisWorkbook.Names("myTypes")

Even if I could bypass it using On Error statements it will occur again in ComboBox1_Change when I populate ListBox1 in a similar manner.

I am guessing that I am not referring properly to the named ranges stored in the add-in.
Perhaps because it is an add-in.
However, if I change the IsAddin to False in ThisWorkbook Properties, then the ComboBox and the ListBox will populate with no error at all.

BTW you can also access the form using the ribbon button I created so as to be closer to the original file where the issue appeared.

Thanks again for your interest and help...

[MrBlackd]

So what do you think about this issue? :confused:

[Admin]

This should work


Dim a

a = ThisWorkbook.Worksheets("DATA").Range("myTypes").Value2

With ComboBox1
.List = a
End With

but not sure why the rowsource property is not working.

[MrBlackd]

I will take a shot and I will report back.
Thanx !!! :cool:


Edit:

It seems to be working great with named ranges and the isaddin = false issue is gone. the userform loads properly and the listbox and combobox populate properly

There is only one disadvantage, if the named range consists of 1 cell only then the listbox does not show anything.
If it can be overcome then it will be perfect.

[Admin]

If IsArray(a) Then
.List = a
Else
.AddItem a
End If

[MrBlackd]

Thanks a million!!!

[Admin]

You are welcome !!! :cheers: