+ Reply to Thread
Results 1 to 4 of 4

Populating ComboBox from range in Worksheet based on another comboBox - Runtime Error: 424

  1. #1
    Registered User
    Join Date
    02-15-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Populating ComboBox from range in Worksheet based on another comboBox - Runtime Error: 424

    Hello All,

    I am fairly new to VBA programming. I've made a userform with multiple comboBoxes. The value selected in the first ComboBox (combtype) is supposed to dictate what the second ComboBox (combloc) is populated with. I know I can do this by adding items individually, however, there are over 500 options (~150 per option in combtype) available in the second combobox (combloc) and I may need to add or adjust those values in the future. I have been unable to get my combloc to populate based on a range of values in the worksheet.

    Here is the part of my userform in question: y.PNG

    I have defined the ranges in the worksheet using Define name in the formulas tab.

    When the userform initializes I want it to set those ranges to be called upon after combtype is selected. Here is the code:

    Private Sub UserForm_Initialize()

    Me.combloc.Enabled = False

    'SINGLE, DOUBLE, FLAT, and OTHER are the options in the first comboBox (combtype)'
    Dim rngSINGLE As Range
    Dim rngDOUBLE As Range
    Dim rngFLAT As Range
    Dim rngOTHER As Range
    Dim wk As Worksheet

    Set wk = Worksheets("Inventory")
    For Each rngSINGLE In wk.Range("SINGLEList")
    Next rngSINGLE

    For Each rngDOUBLE In wk.Range("DOUBLEList")
    Next rngDOUBLE

    For Each rngFLAT In wk.Range("FLATList")
    Next rngFLAT

    For Each rngOTHER In wk.Range("OTHERList")
    Next rngOTHER

    End Sub



    When the option is selected in combtype here is what happens:

    Private Sub combtype_change()

    'These are to prevent the combloc from adding duplicate items if the combtype is changed'
    Me.combloc.Clear
    Me.combloc.Enabled = True

    If Me.combtype.value = "SINGLE" Then

    'Here is where the object required error occurs'
    Me.combloc.AddItem rngSINGLE.value

    ElseIf Me.combtype.value = "DOUBLE" Then

    Me.combloc.AddItem rngDOUBLE.value

    ElseIf Me.combtype.value = "FLAT" Then

    Me.combloc.AddItem rngFLAT.value

    ElseIf Me.combtype.value = "OTHER" Then

    Me.combloc.AddItem rngOTHER.value

    Else
    Me.combloc.Enabled = False
    End If

    End Sub


    I feel like I am close but would appreciate some guidance. Most other threads suggest just populating the comboboxes when the form initializes but that is not possible on my form until combtype is chosen.

    Thanks in advance.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,615

    Re: Populating ComboBox from range in Worksheet based on another comboBox - Runtime Error:

    Your UserForm_Initialize procedure isn't setting those ranges. It isn't doing much of anything, but you don't need it for this case.

    This code (not tested) will use the selected value in the Type combobox and concatenate "List" to the end of the value to define the named range for the bloc combobox.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 03-22-2018 at 11:10 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-15-2018
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Populating ComboBox from range in Worksheet based on another comboBox - Runtime Error:

    /u/AlphaFrog Thank you! That was the solution. I think I was overthinking the problem. But this worked perfectly and will make it much easier on me to edit in the future.

    I appreciate the help and quick reply.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,615

    Re: Populating ComboBox from range in Worksheet based on another comboBox - Runtime Error:

    Glad it worked. Thanks for the feedback.

    You fortunately used that the Type values as the names for the ranges. That made it easy to code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Access combobox controls in worksheet - Getting runtime error '438'
    By padala in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2016, 11:08 PM
  2. populating combobox based on another combobox selection
    By bqheng in forum Word Programming / VBA / Macros
    Replies: 7
    Last Post: 08-24-2015, 08:52 AM
  3. [SOLVED] Mac generating Runtime Error 380 when loading Range into Combobox
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2015, 07:16 PM
  4. Populating Combobox based on Combobox selection from EXCEL worksheet
    By JChaney17 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-04-2014, 08:14 AM
  5. [SOLVED] Populating ComboBox using dynamic named range - Error
    By TC1980 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2014, 05:42 AM
  6. Removing Duplicates from ComboBox and Populating one combobox based on another
    By kbmtech in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2010, 11:17 PM
  7. Populating the list in a combobox based off the value chosen in another combobox
    By Mervil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2010, 11:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1