Listbox Rowsource Not Populating Correctly

stildawn · Aug 22nd 2016

Hi All

I have a list box in which I am trying to show names and email addresses from Sheets("Emails").Range("A:B")

Here is the userform_initialize code:

Code

'##### Fills in email addresses ###############################################
Lastrow = ThisWorkbook.Sheets("Emails").Range("A" & ThisWorkbook.Sheets("Emails").Rows.Count).End(xlUp).Row
If IsEmpty(ThisWorkbook.Sheets("Emails").Range("A1").Value) Then
Else
    With Menu.EmailsList
        .ColumnCount = 2
        .ColumnWidths = "45;95"
        .RowSource = ThisWorkbook.Sheets("Emails").Range("A1:B" & Lastrow).Address
    End With
End If
'##############################################################################

Display More

This basically checks if there are any names and addresses in the 'Emails" sheet, and if so populates the "EmailList" listbox on the userform "Menu".

But the code acts odd, for example if I'm currently on the "Emails" sheet in excel, then the code works fine, however if I am on the "Master" sheet (which is the only sheet the end users will ever see) then the listbox populates range("A1") on the master sheet, which is just a title and is completely not what I want.

I can't see where the listbox is getting this "Master" sheet reference from when populating, all the code clearly references ".RowSource = ThisWorkbook.Sheets("Emails").Range("A1:B" & Lastrow).Address"

What could be going on?

Regards

stildawn · Aug 22nd 2016

Re: Listbox Rowsource Not Populating Correctly

Figured it out, it needed (External:=True) at the end of the address part.

Dont know why though

skywriter · Aug 22nd 2016

Re: Listbox Rowsource Not Populating Correctly

When you use:

Code

ThisWorkbook.Sheets("Emails").Range("A1:B" & Lastrow).Address

Let's say the last row is 100, then the code returns A1:B100, there's no reference to a sheet name just because you are using a sheet name in the code, and the address is simply column letters and row numbers.

Using external adds the sheet name to what the code returns, without it the row source uses the address the code returns which, because it doesn't have a sheet names pulls the data from the active sheet.

It's the same as when you have code that says x = Range("A1").Value.

x will be whatever is in A1 on the active sheet unless I specify a sheet.

stildawn · Aug 26th 2016

Re: Listbox Rowsource Not Populating Correctly

Ah good to know, makes sense now. Thanks

skywriter · Aug 26th 2016

Re: Listbox Rowsource Not Populating Correctly

My pleasure, thanks for the feedback. :cool:

Sharid · Aug 26th 2016

Re: Listbox Rowsource Not Populating Correctly

This should also work, might need to change it a bit

Code

Private Sub UserForm_Initialize()
'Shows product list ROW from sheet to userform listbox1


ListBox1.ColumnCount = 3
    ListBox1.List = Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
    
'Shows product list COLUMN from sheet to userform listbox1
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String
     
     '~~> Change your sheetname here
    Set ws = Sheets("Emails")
     
     '~~> Set you relevant range here
    Set rng = ws.Range("A1").CurrentRegion
     
    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count
         .List = rng.Offset(1).Resize(rng.Rows.Count - 1, rng.Columns.Count).Value
        '.List = rng.Value
         
         '~~> Set the widths of the column here. Ex: For 5 Columns
         '~~> Change as Applicable
        .ColumnWidths = "50;175;60"
        .TopIndex = 0
         
         
    End With
     
     
End Sub

Display More

nimesh29 · Sep 8th 2016

Re: Listbox Rowsource Not Populating Correctly

Quote from Sharid;776347

This should also work, might need to change it a bit

Code

Private Sub UserForm_Initialize()
'Shows product list ROW from sheet to userform listbox1


ListBox1.ColumnCount = 3
    ListBox1.List = Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
    
'Shows product list COLUMN from sheet to userform listbox1
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String
     
     '~~> Change your sheetname here
    Set ws = Sheets("Emails")
     
     '~~> Set you relevant range here
    Set rng = ws.Range("A1").CurrentRegion
     
    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count
         .List = rng.Offset(1).Resize(rng.Rows.Count - 1, rng.Columns.Count).Value
        '.List = rng.Value
         
         '~~> Set the widths of the column here. Ex: For 5 Columns
         '~~> Change as Applicable
        .ColumnWidths = "50;175;60"
        .TopIndex = 0
         
         
    End With
     
     
End Sub

Display More

HI: Is it possible to to have listbox1 to populate from multiple sheets?

Sharid · Sep 8th 2016

Re: Listbox Rowsource Not Populating Correctly

No you will need to make changes to the code, you can either put 2 list boxes or keep 1 and move all the data to 1 sheet.

Listbox Rowsource Not Populating Correctly

Participate now!

Share