Results 1 to 4 of 4

Drop Down list control using VBA

This is a discussion on Drop Down list control using VBA within the Excel Questions forums, part of the Question Forums category; Hi I want to be able to populate a drop down list with its parameters using VBA (from a pre-existing ...

  1. #1
    New Member
    Join Date
    Jul 2004
    Posts
    40

    Default Drop Down list control using VBA

    Hi

    I want to be able to populate a drop down list with its parameters using VBA (from a pre-existing array of values) and then use VBA to read the final selection from the list as made by the user.

    Any advice/coding help that allows to do that?

    Regards,

    G

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,110

    Default Re: Drop Down list control using VBA

    This is basic coding for a listbox (or combo) in a userform. If you use one that refers to a worksheet range you only have to change the contents of that range.

    Code:
    '-------------------------------------------
    '- initialise form
    Private Sub UserForm_Initialize()
        Set DataSheet = ThisWorkbook.Worksheets("data")
        Set MyList = DataSheet.Range("FileList")
        Rw = 1
        FileListBox.Clear
        While MyList.Cells(Rw, 1).Value <> ""
            FileListBox.AddItem
            FileListBox.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
            FileListBox.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value
            Rw = Rw + 1
        Wend
    End Sub
    '----------------------------------
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    New Member
    Join Date
    Jul 2004
    Posts
    40

    Default Re: Drop Down list control using VBA

    Thanks!

    The thing is that I do not want to use a range in excel to populate the drop down list. I am reading a list of countries from an access database and want the drop down list to be populated with the countries without copying the countries list into an excel range. The countries list is stored in an array variable in a VBA module.

    Regards

    G

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,110

    Default Re: Drop Down list control using VBA

    So something like :-

    Code:
    For rw = 1 To 10
        FileListBox.AddItem 
        FileListBox.List(rw - 1, 0) = MyArray(rw)
    Next
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com