Using LastRow Code to Fill ComboBox.List But it Doesn't Work With Only 1 Row of Data

[RunTime91]

Greetings ~

The below code works great as long as there is more than one row of data to populate the List.

CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value

However to insure accurate results when there is only one row of data to populate I use the following and get a "Could Not Set The List Property. Invalid Property Array Index"

If Lr = 2 Then 
CmbCallRsn.List = Ws1.Range("A2").Value
Else
CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value
End If

Thank You for any assistance

RT91

 

[RunTime91]

Still pulls a 'Compile Error' - Expected Function or Variable

 

[Cubist]

Is this ActiveX control for form control combo box?

 

[Cubist]

This works for ActiveX for me and does not need the if statement for Lr =2. Try.

Sub PopulateComboBox()
    Dim Ws1 As Worksheet
    Set Ws1 = ThisWorkbook.Sheets("UndrPaidTxs")
    Dim comboBox As Object
    Dim Lr As Long
    Lr = Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row
 
    Set comboBox = Ws1.OLEObjects("CmbCallRsn").Object
    comboBox.Clear

    For Each c In Ws1.Range("A2:A" & Lr)
        comboBox.AddItem c.Value
    Next c
End Sub

 

[RunTime91]

Hey Cubist ~ It's on a UserForm

 

[Akuini]

@RunTime91
Try:

If Lr = 2 Then 
CmbCallRsn.List = Array(Ws1.Range("A2").Value)
Else
CmbCallRsn.List = Ws1.Range("A2:A" & Lr).Value
End If

 

[RunTime91]

Works Like a Dream, Akuini!!

Thank You Both Akuini & Cubist for helping me on this one

It was much more difficult than I had anticipated it would be...

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.