Populate a combobox into a Userform with a selection of cells in the active sheet

  • Hi!

    I just started to use VBA into excel and I'm trying to create a User Form where I select data from the active sheet (the Userform shall be duplicated once I duplicate the sheet where it is included and all the reference shall be to te active sheet):

    - the Userform is named "SystemDesignUserForm"

    - the actual sheet where it is included is named "System Design"

    - the ComboBox is named "DIAComboBox" and shall list the data included into cells "w6:w33".


    I tried with the code:


    Code
    1. Private Sub SystemDesignUserForm_Initialize()
    2. DIAComboBox.List = Range("W6:W33").Value
    3. End Sub


    and also with


    Code
    1. Private Sub SystemDesignUserForm_Initialize()
    2. DIAComboBox.List = ActiveSheet.Range("W6:W33").Value
    3. End Sub


    and with:


    Code
    1. Private Sub SystemDesignUserForm_Initialize()
    2. DIAComboBox.List = Worksheets("System Design").Range("W6:W33").Value
    3. End Sub



    but it doesn't wotk: the combobox appear void.

    I attach the xlsx file to see what I'm doing.

    Everythink work, except the combobox :(


    Do you have any suggestion?

    Files

    • TEST.xlsm

      (352.17 kB, downloaded 25 times, last: )

    Edited once, last by royUK: add code tags ().

  • Try this


    Code
    1. Private Sub UserForm_Initialize()
    2. Me.DIAComboBox.List = Range("W6:W33").Value
    3. End Sub
    4. Private Sub CLEAR_Button_Click()
    5. ''///Note: don't use user form name
    6. Call UserForm_Initialize_Initialize
    7. End Sub

    Note: I have added code tags for you, please read the Forum Rules to understand why.

  • Thank you Roy,

    and what about if the list shall be linked to a range in a row like AR40:BK40, not in a column? I've tried to modify your code by it doesn't work... It shows only the first cell


    Code
    1. Private Sub UserForm_Initialize()
    2. Me.DIAComboBox.List = Range("W6:W33").Value
    3. 'Fill FWComboBox
    4. Me.FWComboBox.List = Range("AR40:BK40").Value
    5. End Sub

    Thank you for your help

  • Done!

    Code
    1. Private Sub UserForm_Initialize()
    2. Me.DIAComboBox.List = Range("W6:W33").Value
    3. 'Fill FWComboBox
    4. Me.FWComboBox.List = Application.Transpose(Range("AR40:BK40").Value)
    5. End Sub

    Thank you for the support!

    :)