We help IT Professionals succeed at work.
Get Started

Multi-select listbox doesn't allow selection after setting ListFillRange

Last Modified: 2012-05-12

I have a multi-select listbox in Excel that I want to populate using a range name that is generated on the change event of a specific column in a worksheet.  My current code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngPCOL As Long, lngPSROW As Long, lngPEROW As Long
Dim lngPLISTCOL As Long, lngPLISTROW As Long

    lngPCOL = Range("PackageColumn").Column
    If Target.Column <> lngPCOL Then Exit Sub
    lngPSROW = Range("PackageColumn").Row
    lngPEROW = Range("PackageColumn").Rows.Count + lngPSROW - 1
    If Target.Row < lngPSROW Or Target.Row > lngPEROW Then Exit Sub
    lngPLISTCOL = Sheets("Validation").Range("Packages").Column
    lngPLISTROW = Sheets("Validation").Range("Packages").Row
    ActiveWorkbook.Names.Add Name:="PackageList", RefersToR1C1:= _
        "=Validation!R" & lngPLISTROW & "C" & lngPLISTCOL & ":R" & Sheets("Validation").Range("MaxPackage").Value + 1 & "C" & lngPLISTCOL
    lstPACKAGE.ListFillRange = "Validation!" & Sheets("Validation").Range("PackageList").Address ' Sheets("Validation").Range("PackageList").Range
End Sub

Open in new window

The range "PackageColumn" is a contiguous range in a single column on the current sheet.

The range "Packages" is used to mark the top of the list of Packages held behind the scenes on the Validation worksheet.

The range "MaxPackage" is a single cell that uses the Max function to find the highest package number input to the "PackageColumn" range above.

Once the code runs, lstPACKAGE, my listbox, clearly shows all the desired packages, but I can't click on it, scroll with it or do anything useful with it.

This is quite urgent, and though I use Excel a great deal, I can normally make do with forms controls, though I need the multi-select functionality to enable me to summarise values based on more than one package.

Any help gratefully received.
Watch Question
Grand Poobah
Most Valuable Expert 2011
Top Expert 2011
This problem has been solved!
Unlock 1 Answer and 23 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE