VBA Loops

[ryancgarrett]

I have a userform with a listbox and option buttons that populate the listbox from a chart of accounts sheet. Right now my code works and this is what it looks like:

Sub PopulateListBox()
If frmAddAccount.optAsset.Value = True Then
frmAddAccount.lbxAccountName.RowSource = "'Chart of Accounts'!B3:B17"
ElseIf frmAddAccount.optLiability.Value = True Then
frmAddAccount.lbxAccountName.RowSource = "'Chart of Accounts'!B19:B23"
ElseIf frmAddAccount.optOwnerEquity.Value = True Then
frmAddAccount.lbxAccountName.RowSource = "'Chart of Accounts'!B25:B27"
ElseIf frmAddAccount.optRevenue.Value = True Then
frmAddAccount.lbxAccountName.RowSource = "'Chart of Accounts'!B30:B33"
ElseIf frmAddAccount.optExpense.Value = True Then
frmAddAccount.lbxAccountName.RowSource = "'Chart of Accounts'!B35:B53"
End If
End Sub

My problem is that if the user adds more accounts to the program then the range added to the listbox is thrown off. Each account type is assigned a different number, i.e. Assets are between 100 and 199, Liabilities are between 200 and 299 etc. Can I run a loop that determines the range of all asset accounts and then populates the listbox with those accounts if that option is selected, rather than manually defining the range as I have above.

 

[Aaron Blood]

For what you describe using the option buttons, the basic code structure might look something like this:

Sub Populate_ListBox()
    Dim cell As Range, CoA As Range
    
    Set cell = Sht_CoA.Range("B3")
    Set CoA = Sht_CoA.Range(cell, cell.End(xlDown))
    
    With frmAddAccount
        .lbxAccountName.Clear
        For Each cell In CoA
            Select Case cell.Value
                Case 100 To 199
                    If .optAsset Then .lbxAccountName.AddItem cell.Value
                Case 200 To 299
                    If .optLiability Then .lbxAccountName.AddItem cell.Value
                Case 300 To 399
                    'and so on...
                Case 400 To 499
                    'and so on...
                Case Else
                    'and so on...
            End Select
        Next cell
    End With
    
End Sub

Assuming your "Chart of Accounts" worksheet tab has the code name: Sht_CoA (you can adjust that as you see fit)

I also included something (a little crude, but maybe OK for you) that will look at cell B3 and expand the range downward so you can have a variable sized chart of accounts range reference; the line of code is: Set CoA = Sht_CoA.Range(cell, cell.End(xlDown))
(where cell is temporarily assigned to "B3", although I prefer to use defined workbook names for such references in code)

On a side note, I hope you're associating this code with the userform initialize and option button change events (as opposed to a standard code module or the worksheet code module.) In which case you could just ignore or substitute "Me" in place of the references to: frmAddAccount

Also, it would seem a little odd to just have account numbers appear in a single column listbox as opposed to the account number with some sort of a name or description (at least a 2 column listbox).