Drop Down list control using VBA

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Drop Down list control using VBA

  1. #1
    New Member
    Join Date
    Jul 2004
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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)
    www.cycleofgrowth.com
    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,127
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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)
    www.cycleofgrowth.com
    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

User Tag List

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