Problem populating a list box

Folks,
The attached workbook uses two list boxes. List box lstTopics is populated by lstCategory. My problem is that in populating lstTopics I populate the list box except for the last item, regardless of the Category selected
Function-and-Formulas-for-Excel-.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:
I'm working on it.
0
Martin LissOlder than dirtCommented:
BTW if you don't mind I'd like to switch from ActiveX listboxes to Forms ones so as to avoid the problem with the listboxes text becoming tiny
0
Frank FreeseAuthor Commented:
No problem with the change to Forms
Talk tomorrow
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Martin LissOlder than dirtCommented:
Okay I wound up using the ActiveX listboxes after all because the Forms listboxes use a very small font that can't be changed.  All the items were in the list but you just couldn't see the last one because of the IntegralHeight property of the listboxes. When set to True, VBA always shows full rows (by that I mean top to bottom) and when it's false you may get some small fraction of a row showing, or, as was the case in your workbook, nothing at all. You would think then that the solution is to set the IntegralHeight to True but when the listbox is used on a sheet, the listbox then goes really crazy. You can try it but make sure that you DON'T save the workbook.

What I did to fix the problem (while keeping IntegralHeight = False) was to adjust the formulas for the named ranges slightly so that they include a blank row at the end. It's the blank row that now doesn't show up but the one above it does and that's what we want:)

I also added a new named range called "Category" out in column Z so that now the vales in lstCategory are loaded automatically without any code. And I adjusted the width of the columns on the 'Topics' sheet so that the text of each named range fits into one column rather that "slopping over" into the adjacent columns.
Q-28238446.xlsm
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 like the idea of a blank line in the lstTopics.

In opening the workbook as presented and without resaving it I loose the tabs and the scroll feature on the list box, but I trust they're working fine for you.

One of the properties in the list boxes I had to change was setting the Locked property to True. At False everytime I selected an item from a list box the box shrink until there was no box. This is simply informational.

You've been right on so far and I would close this question and move on. The next question is once a Tpoic has been select then the GoTo select command box is enabled.

After that question consider this as a question. Once the GoTo command button is enabled as a new command button labeled "Change Category or Toice" would be added and enabled. This would allow the user the opportunity if they change their mind based upon the description and wanted to select another Category or Topic.

Following that question we can then have a question that will locate the item selected (=====>) so the user knows where it is they will be going. I planned on making the description more meaningful anyway.

OK to go forward?
The problem I have is what workbook do I post? The one you just sent back and saved  under a different name?
0
Martin LissOlder than dirtCommented:
In opening the workbook as presented and without resaving it I loose the tabs and the scroll feature on the list box, but I trust they're working fine for you.
I don't know what that means, but in any case yes, it's OK to go forward. You should post the workbook I posted (renamed if you like) but I don't plan on downloading it and instead I'll just use mine.
0
Frank FreeseAuthor Commented:
Great job
Many thanks
0
Frank FreeseAuthor Commented:
Question will be posted shortly
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.