Populating a ListBox not working

Folks,
Check the attached workbook. I cannot get the first list box to populate and I do not know why?
List.xlsm
Frank FreeseAsked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
The listboxes are filled using Named ranges and when you copy the controls or the sheet from one workbook to another things can get messed up. Go to Formulas and then choose Nazme Manager which in Excel 2010 looks like this.
Name Manger
You'll find that the 'Refers To' for the names are referring to things that may not exist. In any case copy the formulas from my worksheet to yours and it should be OK.
0
Martin LissOlder than dirtCommented:
You'll also need to change the sheet name where the source for the listitems in the copied formulas so they match the names in your workbook.
0
Martin LissOlder than dirtCommented:
In other words the formula in Name Manger for the "Formulas" Named Range should be


=OFFSET(Sheet2!$A$1,0,0,MATCH("*",Sheet2!$A:$A,-1),1)

if the data is on Sheet2.
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Frank FreeseAuthor Commented:
For the life of me, I followed step-by-step and still cannot get the first list box to populate
List.xlsm
0
NorieAnalyst Assistant Commented:
What is the first listbox supposed to be populated with?
0
Martin LissOlder than dirtCommented:
The first listbox is populated via

Listbox1.AddItem "Functions"

Etc
0
Frank FreeseAuthor Commented:
I'm using the following code:

Private Sub ListBox1_Click()
Select Case ListBox1.ListIndex
    Case 0
        ListBox2.ListFillRange = ""
        ListBox2.ListFillRange = "Formulas"
    Case 1
        ListBox2.ListFillRange = ""
        ListBox2.ListFillRange = "LogicalFunctions"
    Case 2
        ListBox2.ListFillRange = ""
        ListBox2.ListFillRange = "TextFunctions"
End Select
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
We're cross-posting but you code to fill lidtbox2 is correct but make sure that the named ranges are defined correctly. Listbox1 is filled as I showed above in Workbook_Open.

I'm not at home right now and if you need me to I can correct your WB when I get home in an hour or so.
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
Frank FreeseAuthor Commented:
I just found the error and it concurrs with your finding. Great job!
0
Frank FreeseAuthor Commented:
Good catch! Thanks
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
NorieAnalyst Assistant Commented:
I'm confused, where's the code to populate ListBox1?
0
Martin LissOlder than dirtCommented:
It's in his Workbook_Open sub.
0
NorieAnalyst Assistant Commented:
Martin

There's nothing in the ThisWorkbook module.
ThisWorkbookModule.JPG
0
Martin LissOlder than dirtCommented:
That's because the workbook he posted in this thread was incomplete. I've been working with him in other threads and it was in a sample I proved him which had this code.

Private Sub Workbook_Open()
With Sheets("Sheet1")
    With .ListBox1
        .Clear
        .AddItem "Formula"
        .AddItem "Logical Function"
        .AddItem "Text Function"
    End With
End With
End Sub

Open in new window

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.