A descriptive box needed

Folks,
The attached workbook, worksheet "Menu" uses two list boxes. List box lstTopics is dependent upon lstCategory. What I would like to be able to do is when a user selects a topic additional information is available about the topic so the user can select the correct topic.
Excel-Formulas-and-Functions-Rev.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:
Do you want the "additional informatiom" and the two listboxes to remain on the sheet or would you like them move to a userform?
0
Martin LissOlder than dirtCommented:
If the listboxes remain on the sheet further code will apparently be needed to prevent the text in those listboxes from becoming smaller.
0
johnnyho_Commented:
Hi fh_freese,

not sure if I correctly understood your requirement, you may just add a new textbox.

Then, into each case in procedure "lstCategory_Click" you would add a line to populate this textbox with a description of the category (see. Of course, you may externalize the texts for easier editing.

Private Sub lstCategory_Click()
Select Case lstCategory.ListIndex
    Case 0
        lstTopic.ListFillRange = ""
        lstTopic.ListFillRange = "Formulas"
        TextBox1.Value = "Description of the formulas"
        '******* Q2 Start *******
        intCatFactor = 10
        '******* Q2 End *********

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Martin LissOlder than dirtCommented:
This currently only works for the first two topics in Formulas.
Q-28239708.xlsm
0
Frank FreeseAuthor Commented:
I've seen something like this which is using a userform.

Descriptive box
0
Frank FreeseAuthor Commented:
MartinLiss:
I did not see you Proof of concept until my last post. I like what you proposed very much
0
Frank FreeseAuthor Commented:
MartinLiss:
It appears that all I need to do is expand the descriptions. I'm not sure if there's anything to ask?
0
Martin LissOlder than dirtCommented:
In my proof of concept I just quickly added a new Named range in column H of the Topics sheet. In "real life" I would instead add a new column after each existing column which would hold the descriptions for each topic. That would be better than having all the descriptions in column H. Would you like me to do that now?

Do you have any problems currently with the text in lstCategory and lstTopic getting small?
0
Frank FreeseAuthor Commented:
Yes and No
Make it so Scotty
0
Martin LissOlder than dirtCommented:
Aye Captain as long as the dilithium crystals hold out.
0
Martin LissOlder than dirtCommented:
Here's my solution. All the changes are marked with "Q3". If you go to the Topics sheet you'll see that I added a new column next to each Category column. You should replace the few I put there (and all the rest) with whatever you want the description to be. I also added a 'RestoreControls' macro that will reset the 2 listboxes and the new lblDescription to their original settings if they get to small or otherwise misbehave. The macro is assigned to Ctrl+Shift+R.

Note that currently my descriptions will only show up for the first two Formulas choices and the first choice for the rest of the Category choices.
Q-28239708.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:
Hey, Alabama won in a close game
Oregon had Tennessee for a snack
UCLA did a job on Nebraska
And you had enough dilithium crystals to hold out

I know I have a lot to do now and the functionalty sure is different than when we started. You're a good man Martin and truly appreciated
I'll be posting more questions soon:
1. A Search routine incorporating a Find All function.
2. Protecting the workbook (I know how to do it, however, I want to do it the right way)
3. Compiling and making this an executable - maybe there's something here to partnership with as a marketable tool
4. Possibly a Print command button for each worksheet

For now, I can't send you any BBQ so I can't help you on you 1, 2, 3 list, so enjoy # 3.
Thank you again -
Frank
0
Frank FreeseAuthor Commented:
A fantastgic job
Well done and very professional
Many thanks - you are a real asset to Expert Exchange.
0
Martin LissOlder than dirtCommented:
#3 is on the menu for tonight:) What would the 'Find All' search against?

You're welcome and I'm always glad to be able to help.

Marty - MVP 2009 to 2013
0
Frank FreeseAuthor Commented:
"What would the 'Find All' search against?" Not sure of the question here?
0
Martin LissOlder than dirtCommented:
Above you said "A Search routine incorporating a Find All function" and I was asking where the search would look. Column B? Or C? Or lstTopic or???
0
Frank FreeseAuthor Commented:
Great question! Maybe I'm building too much functionality.

Let's say that the user determines if they want to "Search by Topic" or "Search by Purpose" or "Find All"? If they select "Topic" or "Purpose" and a match is found they can accept and click on the topic to go to it, else continue. However, if they select "Find All" it can get messy. They could be offered the option to "Find All by Topic"  or "Find All By Purpose". The Find All could display all the Topics or Purposes and the user selects the one they want to go to.

BTW, I changed at B9 to read "Formula or Function Topic" and at C9 to read "Purpose" from what I had.

I like my idea but it is too much for one question but maybe three of four?
0
Martin LissOlder than dirtCommented:
What would be the difference between for example "Find byTopic" and "Find All by Topic"?
0
Frank FreeseAuthor Commented:
The only difference is one would find each single occurence while the list ALL would list them all in a separte list box.
0
Martin LissOlder than dirtCommented:
It's your project of course but IMO if you want a search function there should be just two choices and those would be the two you currrently call Find All by Topic and Find All by Purpose but I would drop the "All". Also I don't think you would need a separate listbox since we could either filter lstTopic or filter the rows.
0
Frank FreeseAuthor Commented:
I have trusted your opinion and will go with your suggestion. I would like to post antoher question adding this to the Menu worksheet. Are you OK with that?
0
Martin LissOlder than dirtCommented:
You should just ask questions whenever you like and unless someone else has already given a better solution than what I could come up with I'll try to answer. Unless of course the question is out of my area of expertise as it will be if you ask about making the wb into an exe.
0
Frank FreeseAuthor Commented:
Thanks
0
Frank FreeseAuthor Commented:
Martin,
I need a little help please.
I'm adding another topic. When I select the Category I'm interested in the topic does not appear. I've been careful to match the topics list to correspond with the menu list. I've added a Case 6 to the  Private Sub lstCategory_Click() and Private Sub lstTopic_Click() sub-procedures.
I've attached the file I'm working with - please advise
Function-and-Formulas-for-Excel-.xlsm
0
Martin LissOlder than dirtCommented:
In lstCategory_Click() and lctTopic_Click things like 'DateTimeFunctions' are Named Ranges.  A Named range is a range of cells to which you give a name for easy reference. If you want to look at the existing Named Ranges go to some sheet other than "Menu" and select Formulas|Named Ranges. If you then select the DateTimeFunctions Named Range and click in the 'Refers To' area as indicated by the arrow, you'll see that the Named range is selected as this picture shows.

Named range
So to fix your problem you need to add a new data in the 'Topics' sheet if you haven't already done that. Then you need to select the 1st column of that data (there should be two columns) and add a new Named Range by going to Name Manager and selecting 'New'. Once you've done that go to any of the other named ranges for that sheet, copy the 'Refers To' text, paste it into the 'Refers To' text for the new Named Range, and modify it to reflect the correct column.
0
Frank FreeseAuthor Commented:
Thanks Martin - you done educated me again. Have a good evening now. I got it working.
I do appreciate this
0
Martin LissOlder than dirtCommented:
YW
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.