Populate Combobox

[ryancgarrett]

I have the following code to populate some combo boxes on a form, however for some reason it always references the active sheet, rather than the "Data Sheet" like I want it to. Anyone see anything wrong?

Sub populateComboBox()
   Dim lr As Long
    
    With Sheets("Data Sheet")
        lr = BlankRow("Data Sheet", 2, 4) - 1
        
        frmTransactionEntry.cbxAccount1.RowSource = .Range("D2:D" & lr).Address(False, False)
        frmTransactionEntry.cbxAccount2.RowSource = .Range("D2:D" & lr).Address(False, False)
        frmTransactionEntry.cbxAccount3.RowSource = .Range("D2:D" & lr).Address(False, False)
        frmTransactionEntry.cbxAccount4.RowSource = .Range("D2:D" & lr).Address(False, False)
        frmTransactionEntry.cbxAccount5.RowSource = .Range("D2:D" & lr).Address(False, False)
        frmTransactionEntry.cbxAccount6.RowSource = .Range("D2:D" & lr).Address(False, False)
        
    End With
End Sub

'Function to find the end of a list
Function BlankRow(sName As String, sRow As Integer, sCol As Integer) As Integer
    Do Until Sheets(sName).Cells(sRow, sCol).Value = Empty
        sRow = sRow + 1
    Loop
    
    BlankRow = sRow
End Function

 

[Sektor]

This is because Address property returns address WITHOUT sheet name!

[COLOR="Blue"]Sub[/COLOR] populateComboBox()
   
   [COLOR="Blue"]Dim[/COLOR] lr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    
    [COLOR="Blue"]With[/COLOR] Sheets("Data Sheet")
        lr = .Range("D2").End(xlDown).Row
        frmTransactionEntry.cbxAccount1.RowSource = "'Data Sheet!'" & .Range("D2:D" & lr).Address([COLOR="Blue"]False[/COLOR], [COLOR="Blue"]False[/COLOR])
        frmTransactionEntry.cbxAccount2.RowSource = "'Data Sheet!'" & .Range("D2:D" & lr).Address([COLOR="Blue"]False[/COLOR], [COLOR="Blue"]False[/COLOR])
        frmTransactionEntry.cbxAccount3.RowSource = "'Data Sheet!'" & .Range("D2:D" & lr).Address([COLOR="Blue"]False[/COLOR], [COLOR="Blue"]False[/COLOR])
        frmTransactionEntry.cbxAccount4.RowSource = "'Data Sheet!'" & .Range("D2:D" & lr).Address([COLOR="Blue"]False[/COLOR], [COLOR="Blue"]False[/COLOR])
        frmTransactionEntry.cbxAccount5.RowSource = "'Data Sheet!'" & .Range("D2:D" & lr).Address([COLOR="Blue"]False[/COLOR], [COLOR="Blue"]False[/COLOR])
        frmTransactionEntry.cbxAccount6.RowSource = "'Data Sheet!'" & .Range("D2:D" & lr).Address([COLOR="Blue"]False[/COLOR], [COLOR="Blue"]False[/COLOR])
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[ryancgarrett]

Ok... So what do I need to do to fix this?

 

[Sektor]

Pardon? I posted fixed code in my previous post!

 

[ryancgarrett]

Sorry, the code didn't load until I refreshed the page. I put in your changes, but I'm getting an error: Could not set the RowSource property. Invalid property value.

 

[ryancgarrett]

I figured it out. Everything was correct except your ! was before the ' rather than after. Thanks for your help!

 

[Sektor]

Ah, yes... Mixed up positions... Thanks for reply!