• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

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,
0
rckrch
Asked:
rckrch
  • 6
  • 4
  • 3
1 Solution
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now