information from a range using a loop best practice for combo box

Question

I wanted to take information from a range and add it to a userform in a combobox.

The below code did that for me with success, but in the combo box I set a range of 40 rows, so it has my values and then after my last value it has an empty space of around 20 lines which I put there in case the user adds more cost centres.

How can the combobox select the exact rows and then recognise, if a new cost centre is added later?

"rules" is the worksheet I get information from, "Cost Centres" is the name of a range I built on that worksheet which has 40 rows.

What is best practice that more than 40 cost centres get added, that I'll never need to go back into the code to change the range?

Private Sub UserForm_Initialize()

    Worksheets("Rules").Activate

    Dim rangeCount As Integer
    Dim Range As Range
    Dim i As Integer

    Set Range = ActiveSheet.Range("A2")
    rangeCount = ActiveSheet.Range("CostCentres").Count
    i = 2

    Do While i <= rangeCount

         Me.CostCentreCMBox.AddItem Cells(i, "a").Text

         i = i + 1
    Loop

End Sub

COnvert the range of cells containing your cost center numbers to a table. Make sure your range name points to the body of the table. Now if you add or remove rows from the table, the range name will also grow or shrink. Then use `Me.CostCentreCMBox.List = Worksheets("Rules").Range("CostCentres").Value` to fill the combo in one line of code. — jkpieterse, May 24 '19 at 15:41

score 0 · Answer 1 · answered May 24 '19 at 17:06

For best practices, you want to avoid using select/activate, use clearly defined and named variables, don't use any Magic Numbers (which are basically just hard-coded values, so avoid hard-coding whenever possible), and comment your code so that when you (or anyone) are looking at it again after forgetting it exists, the code can easily be understood and followed. Even better if the variables used make the code itself easy to follow so that comments aren't needed as much to follow along easily. It could definitely be argued that I have too many comments in this code.

There are many ways to get the last populated cell in a column, but the most recommended method is to use Range.End(xlup) as shown here:

Private Sub UserForm_Initialize()

    Dim oCMBCostCentres As ComboBox
    Dim wsRules As Worksheet
    Dim sCostCentresCol As String
    Dim lDataStartRow As Long

    Set oCMBCostCentres = Me.CostCentreCMBox

    'Adjust these as needed
    Set wsRules = ThisWorkbook.Worksheets("Rules")  'This is the worksheet where your Cost Centres list is stored
    sCostCentresCol = "A"   'This is the column where the Cost Centres list is stored
    lDataStartRow = 2       'This is the row where the Cost Centres list begins (actual data, not the header row)

    'Using the defined variables, work with the Cost Centres list
    With wsRules.Range(wsRules.Cells(lDataStartRow, sCostCentresCol), wsRules.Cells(wsRules.Rows.Count, sCostCentresCol).End(xlUp))
        'Verify it actually has data in it
        If .Row >= lDataStartRow Then
            'Data found, check if it only contains a single cell or not
            Select Case .Cells.Count = 1
                Case True:  oCMBCostCentres.AddItem .Value   'Only a single cell, need to use the .AddItem method
                Case Else:  oCMBCostCentres.List = .Value    'More than one cell, can use the .List method
            End Select
        End If
    End With

End Sub

score 0 · Answer 2 · answered May 28 '19 at 10:23

Thankyou tiger,

that has worked a perfectly,

however I have come across one problem, the code you gave worked perfectly for my cost centre's,

I then on the same user form created a Vendor combobox, which again has worked great with same code just changing variables to right ones. With in the combo box I have all the vendors I need so it works however the scrollbar in that combo box doesn't work. To scroll down the vendors, I have to use arrows keys, or click inside the actual scroll bar bit and move the bar down that way, instead of click and hold the actual bar and move down if you understand.

have you come across this before? is there any reason for it?

regards, Ross

information from a range using a loop best practice for combo box

2 Answers2