Results 1 to 9 of 9

Thread: populate control from named range stored in addin

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0

    Question populate control from named range stored in addin

    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.
    Keep in mind all vba I know has been googled...

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Could you please upload the workbook ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0

    Cool An example of what I mean

    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:
    Code:
    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...
    Attached Files Attached Files
    Last edited by MrBlackd; 05-07-2016 at 10:46 PM. Reason: provide detailed description and attachment
    Keep in mind all vba I know has been googled...

  4. #4
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    So what do you think about this issue?
    Keep in mind all vba I know has been googled...

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    This should work

    Code:
        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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    I will take a shot and I will report back.
    Thanx !!!


    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.
    Last edited by MrBlackd; 05-11-2016 at 12:22 AM.
    Keep in mind all vba I know has been googled...

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Code:
        If IsArray(a) Then
            .List = a
        Else
            .AddItem a
        End If
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    Thanks a million!!!
    Keep in mind all vba I know has been googled...

  9. #9
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    You are welcome !!!
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. setting loop code to check all named sheets
    By peter renton in forum Excel Help
    Replies: 9
    Last Post: 01-30-2014, 03:01 AM
  2. Replies: 15
    Last Post: 01-07-2014, 12:42 AM
  3. Replies: 4
    Last Post: 07-02-2013, 11:32 AM
  4. Populate data in form
    By Ryan_Bernal in forum Excel Help
    Replies: 4
    Last Post: 02-01-2013, 10:18 AM
  5. Replies: 4
    Last Post: 06-07-2012, 09:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •