Populate list using worksheet references

[lukecthomas]

I have a form with five option buttons and a list box. When each one is selected it is suppose to give a list of accounts depending on the button selected. The lists are derived from a worksheet that lists hundreds of numbered accounts. I've made two loops that find the beginning and ending rows for each category. From here I'm trying to take those row cell references and put everything in between in a list when one of the option buttons is clicked. I'm guessing I need a with statement and a for loop inside, but 'm not quite sure about all the syntax with lists. Please Help.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Below is an example that shows a syntax for populating lists from a range.

Public Function Popluate_List(lCategory As Long)
    Dim rListRange As Range
    '---your code to set rListRange based on lCategory
    '---option button selected, for example...

    With Sheets("Sheet1")
        Select Case lCategory
            Case 1
                Set rListRange = .Range("A201:A300")
            Case 2
                Set rListRange = .Range("A301:A400")
               '----continue for all options
        End Select
    End With

    '----clear and populate listbox
    With UserForm1.Listbox1
        .Clear
        .List = rListRange.Value
    End With
End Function

Call the function with your OptionButton Click events

Private Sub OptionButton1_Click()
    Popluate_List (1)
End Sub
Private Sub OptionButton2_Click()
    Popluate_List (2)
End Sub

 

[lukecthomas]

Thanks so much for the reply! It worked beautifully.