VBA to populate listbox with multiple columns from dynamic rowsource on another spreadsheet

[carlyjay]

Please help, I have been scouring the internet for 2 days and cannot find the answer!

I want to populate a userform listbox that has 2 columns which is looking at a dynamic range on another workbook. I have managed to populate the first column, which is column A, but cannot make it look at column B as well.

The code I have is:

Private Sub UserForm_Initialize()Workbooks.Open "C:\Users\Carly\Desktop\Carly\MASTER.xlsx"
Worksheets("Companies").Activate
            If Workbooks.Count <= 3 Then
    Application.Visible = False
Else
    Windows("MASTER.xlsx").Visible = False
    
End If


Dim compRange As Range


On Error GoTo ErrorHandle


Set compRange = Workbooks("MASTER.xlsx").Worksheets("Companies").Range("A2")


If Len(compRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If


If Len(compRange.Offset(1, 0).Formula) > 0 Then
   Set compRange = Range(compRange, compRange.End(xlDown))
End If


CompaniesListBox.RowSource = compRange.Address


BeforeExit:
Set compRange = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit


End Sub

any help would be greatly appreciated!!

Thanks in advance

 

[CalcSux78]

Welcome to the forum.

Perhaps reading up on listboxes here may help: http://www.globaliconnect.com/excel...roperty-listindex-rowsource-selected-property

Since you arer setting the RowSource property, it would be best to set your compRange to a two column range. If your data for the second column of the listbox isn't in Column B, I would leave the RowSource blank and assign data to CompaniesListBox via a loop

 

[carlyjay]

Welcome to the forum.

Perhaps reading up on listboxes here may help: http://www.globaliconnect.com/excel...roperty-listindex-rowsource-selected-property

Since you arer setting the RowSource property, it would be best to set your compRange to a two column range. If your data for the second column of the listbox isn't in Column B, I would leave the RowSource blank and assign data to CompaniesListBox via a loop

Thank you for your response, I am able to populate a listbox with 2 columns, my problem is that I cannot seem to make this work with a dynamic range in another workbook.

The data for the second column of the listbox is in Column B, but rowsource properties box will not work with a dynamic range.

 

[dmt32]

I am able to populate a listbox with 2 columns, my problem is that I cannot seem to make this work with a dynamic range in another workbook.

The data for the second column of the listbox is in Column B, but rowsource properties box will not work with a dynamic range.

Hi,
done very quickly & untested but see if this update to your code does what you want

Private Sub UserForm_Initialize()
    Dim compRange As Range
    Dim wbMaster As Workbook
    Dim FileName As String
    
    FileName = "C:\Users\Carly\Desktop\Carly\MASTER.xlsx"
    
    On Error GoTo ErrorHandle
    
    Set wbMaster = Workbooks.Open(FileName, ReadOnly:=False)
    If Workbooks.Count <= 3 Then
        Application.Visible = False '<< not sure why doing this?
    Else
        wbMaster.Visible = False
    End If
    
    With wbMaster.Worksheets("Companies")
        Set compRange = .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1, 2)
    End With
    
    If Len(compRange.Cells(2, 1).Formula) = 0 Then
        MsgBox "The list is empty", 64, "List Empty"
        
    ElseIf Len(compRange.Cells(3, 1).Formula) > 0 Then
        
        With CompaniesListBox
            .ColumnCount = 2
            .ColumnWidths = "50;50"
            .List = compRange.Value
        End With
         
    End If
    
    
ErrorHandle:
    Set compRange = Nothing
'close workbbook?
    'If Not wbMaster Is Nothing Then wbMaster.Close False
    Application.Visible = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[carlyjay]

Hi,
done very quickly & untested but see if this update to your code does what you want

Private Sub UserForm_Initialize()
    Dim compRange As Range
    Dim wbMaster As Workbook
    Dim FileName As String
    
    FileName = "C:\Users\Carly\Desktop\Carly\MASTER.xlsx"
    
    On Error GoTo ErrorHandle
    
    Set wbMaster = Workbooks.Open(FileName, ReadOnly:=False)
    If Workbooks.Count <= 3 Then
        Application.Visible = False '<< not sure why doing this?
    Else
        wbMaster.Visible = False
    End If
    
    With wbMaster.Worksheets("Companies")
        Set compRange = .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1, 2)
    End With
    
    If Len(compRange.Cells(2, 1).Formula) = 0 Then
        MsgBox "The list is empty", 64, "List Empty"
        
    ElseIf Len(compRange.Cells(3, 1).Formula) > 0 Then
        
        With CompaniesListBox
            .ColumnCount = 2
            .ColumnWidths = "50;50"
            .List = compRange.Value
        End With
         
    End If
    
    
ErrorHandle:
    Set compRange = Nothing
'close workbbook?
    'If Not wbMaster Is Nothing Then wbMaster.Close False
    Application.Visible = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

So simple! Thank you so much Dave, I cannot tell you how much stress I went through to get that!

You are a lifesaver

 

[dmt32]

So simple! Thank you so much Dave, I cannot tell you how much stress I went through to get that!

You are a lifesaver

You are most welcome glad suggestion helped you.

Many thanks for feedback - very much appreciated.


Dave