Excel ListBox multiple ranges

I am trying to fill a listbox control in excel VBA with 2 separate ranges.  Can some tell me why it does not work?  The part of the code where SelectedItem.Name  = "Page1" works.  It does not work for Page2 or Page3.  When Page2 is selected the listbox goes bank.

If MultiPage1.SelectedItem.Name = "Page1" Then
    LastAddress = Sheet1.Range("C1000000").End(xlUp).Address
    ListBox1.RowSource = "Sheet1!B6:" & LastAddress
    ElseIf MultiPage1.SelectedItem.Name = "Page2" Then    
    LastAddress = Sheet1.Range("B1000000").End(xlUp).Address
    LastAddress2 = Sheet1.Range("D1000000").End(xlUp).Address
    ListBox1.RowSource = "Sheet1!B6:" & LastAddress & ",Sheet1!D6:" & LastAddress2              
    ElseIf MultiPage1.SelectedItem.Name = "Page3" Then
    LastAddress = Sheet1.Range("E1000000").End(xlUp).Address
    ListBox1.RowSource = "Sheet1!D6:" & LastAddress
    End If

If anyone can help I would appreciated it.

Thanks,
rckrchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Can you upload a sample file for testing?
0
rckrchAuthor Commented:
It is just a simple listing of 4 columns.  B column is date list, C column is some number list, D column is some number list, E column is some number list.
0
Saqib Husain, SyedEngineerCommented:
No it is not. It also has controls. If you upload something I can try to attempt it.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

rckrchAuthor Commented:
Here you go.
0
Saqib Husain, SyedEngineerCommented:
Are you sure?
0
rckrchAuthor Commented:
0
Saqib Husain, SyedEngineerCommented:
This file does not contain the control(s)
0
Roy CoxGroup Finance ManagerCommented:
A MultiPage cannot have a SelectedItem. Do you mean that you have a listBox on the Multipage?
0
rckrchAuthor Commented:
The listbox population is triggered by the selected tab in the multipage.  The page selection name property is used to update the listbox for the appropriate data.  This works on all cases were the ranges are directly adjacent to each other (B and C column), but when the two ranges are separated (B and D column) it does not work.

Thanks for the response.
0
Roy CoxGroup Finance ManagerCommented:
You cannot have two rowsources to populate the ListBox into separate columns so you should use one range. Try this

Option Explicit

Private Sub MultiPage1_Change()
    Dim rRng As Range
    With Sheet1
        Me.MultiPage1.ColumnCount = 2
        Select Case Me.MultiPage1.Value
        Case 0
            Set rRng = .Range(.Cells(6, 2), .Cells(.Rows.Count, 3).End(xlUp)).Value
            Me.MultiPage1.ColumnCount = 2
        Case 1
            Set rRng = .Range(.Cells(6, 2), .Cells(.Rows.Count, 4).End(xlUp)).Value
            Me.MultiPage1.ColumnCount = 3
            Me.ColumnWidths = "50;0,50"    '/// note column 2 is set to zero width to hide it
        Case 2
            Set rRng = .Range(.Cells(6, 4), .Cells(.Rows.Count, 5).End(xlUp)).Value
            Me.MultiPage1.ColumnCount = 2
        End Select
    End With
    Me.ListBox1.List = rRng.Value
End Sub

Open in new window


Where you use Columns B & D it will use B,C & D but B will be hidden in the ListBox.

If this doesn't work attach an example file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rckrchAuthor Commented:
Thanks Roy.  The column width did the trick.  Something I did not think of.  Thanks very much your reply.
0
rckrchAuthor Commented:
Thanks - perfect answer.
0
Roy CoxGroup Finance ManagerCommented:
Glad it helped
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.