Avatar of Frank Freese
Frank FreeseFlag for United States of America asked on

Add a search functionality

I would like to be able to search through my topics and select from the search as an option to the combo box
Excel-Formulas-and-Functions.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

I'll have a demo in a couple of minutes.
Martin Liss

Okay here's a "proof of concept". The only parts that work now are looking for a match in column B to something that you put the 1st Search term, and the Find, Go, and Exit buttons. I'm cooking for some guests tonight so starting in a couple of hours I'll be checking out but check it out and give me your thoughts on turning on the Go To Selection button when something is found as opposed to pressing 'Go' and anything else you think of. Note that my intension for the 'Function or Formula Details' option button is to find for example "birthday" which would take the user directly to the Cal the Age of Person in Days sheet.
Q-28233571.xlsm
ASKER
Frank Freese

What's for dinner?
I'm working on the last worksheets in the Time and Date Functions then I've promised the "boss" a dinner out tonight. She's earned it so I'll look at what you've done after church tomorrow.
Thanks
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Martin Liss

I'm grilling some brined and then spice rubbed pork chops to be served with a Greek salad (all the ingredients except the Feta cheese from our garden) and sweet potatoes also from our garden.
ASKER
Frank Freese

Next time you're in Memphis let me know. I'll treat you to some real BBQ
Martin Liss

I'd love that. Both my wife and I love BBQ. In the order of things we love it's

1.    Pulled Pork
2.    Ribs
3.    Sex
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Frank Freese

I can only help you with the first two, but our Memphis ribs are just incredible.
I guess you'll just have to work out # 3 however :)
ASKER
Frank Freese

On a more different matter, I thought I left enough space to expand but I did not. Continue with the search option for this thread, but consider this big change:
Different questions for both:
1. Two combo boxes instead of the 14 I've planned
2. Combo box one would list the different categories; i.e. Formulas, Text Functions, etc
3. Combo box two would list all the topics associated with the first combo box, i.e. Production Plan, Calculate the age of a person in days.
4. I would continue building the items on tab Menu and using the ListSheets

If I'd built this in Access it would be the same as Combo Box 2 is dependent upon Combo Box 1
Let me know your thoughts, please.
Martin Liss

Let me finish the Search and I'll get back to you.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Frank Freese

Understood
I looked at the Search and overall it looks good what you've done
Martin Liss

I'll post a new workbook in an hour or so.
Martin Liss

This is taking longer than I thought because of the two-search term lookup I want to do which I've never done before. I have to stop for a while so I'll post what I have so far. I think everything works except when you try to look for two things. I eliminated the "And", "Or", "Both" radio buttons that I had in the proof of concept because first of all after I put two seconds thought into it, I realized that "And" and "Both" are the same, and while if you want to do "Or" it's probably technically possible but you can do two searches instead.

Let me know if you find any bugs and/or any changes you might want.
Q-28233571.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Frank Freese

The Search for Name or Function looks good except that once you select Go the Search dialog box does not close. This maybe because you're allowing to continue to the next occurence without having to go back to the Menu tab if that was not what they were looking for?
The Search for Purpose for Formula did not find anything at all if one  did not select both the Name of Function or Formula and Purpose for Formula. I would be expecting that if one was searching for Purpose for Formula they would simply check that option and not have to check the Name of Function or Formula also.
The search for Function or Formula Details searches the workbook and even though it found the text in a worksheet it looks confusing. I can see what your objective is here but would the user understand?
I like the Search function though.
ASKER
Frank Freese

Want a different question?
Martin Liss

Not right now. The Search function has been difficult but hopefully I'll be done with it today.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Frank Freese

You've worked so hard on this. The next question will be a piece of cake. Thanks
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Martin Liss

BTW the two-criteria search now seems to work. I say "seems" because it proved to be VERY difficult and while I tested it you should too.
ASKER
Frank Freese

I'll look at it shortly
Sounds like you got yourself educated
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Frank Freese

I beat this to death and it looks OK - of course some user will come along and do something no one thought of but I'm OK with the Search function. Thanks a bunch.
Ready for the next challenge?
ASKER
Frank Freese

A wonderful solution
Great job!
Martin Liss

Sure but I'm still open for ideas concerning the search for details.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Frank Freese

OK, here's the deal
I'll submit new queastion with a new workbook. This one is where combo box 2 is populated based upon combo box 1. Although I've got combo box1 to populate with catergories, I can not sem to populate combox box 2, topics. Once I select a topic the everything else functionsas before - maybe. See the last tab in this workbook.
What I'm wondering is this - getting rid of everything on the Menu tab from row 10 down replacing the contents from the last tab? This is quite a change.
In the meantime my first question will be to populate a combo box
I'll follow-up with populating a combo box (topics) based upon the a selected  category.
Confused?
I'll explore more on the Search function tomorrow. We've been given our monthly assignments at work this afternoon for September so I will be in and out of contact.
Martin Liss

Something like this?
Listboxes.xlsm
ASKER
Frank Freese

You asked about building the Search - well here you go.
In another question I can ask to have a Find All option and then select the one in the list.
How's that?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Frank Freese

Just looked at the Listboxes you sent - I'm ok with it.
Now let me open another question when once you select an topic the GoTo command button is enabled and the user can then GoTo that worksheet. OK?
That's two items on your plate - since I'm building the worksheets after the Menu tab it the direction you want to choose will not slow me down. I can send you the latest workbook I have. Please advise
But you do not need to continue on this thread for the other two items. It is not fair
Martin Liss

I'm going to be away starting tomorrow and I will return on Saturday. I agree that new questions are called for and I'll work on them when I get back but I need more detail because I don't completely understand your comments above.
ASKER
Frank Freese

no problem. let me know when you're back and we will pick up from there
Have a great and safe trip
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Frank Freese

Could you help me on a problem with the ListBoxes
I've attached a workbook that I created and my Listboxes are not working like yours?
I've used your code but I cannot seem to populate the first list box on the categories.
The only thing I know is I'm unable to change the prfoperty of the TopIndex from -1 to 0?
List.xlsm
Martin Liss

I'll give you the answer here if you like but I would of course prefer a new question.
ASKER
Frank Freese

New question on its way - now
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

I'm glad I was able to help you here.

Marty - MVP 2009 to 2013
Martin Liss

I'm back a little early so if there's anything more you need just fire away.
ASKER
Frank Freese

Welcome back...
You gave me too much time to think so don't get toooooo mad at the changes to the workbook I've been working on as I build my wish list. Let me explain:

1. I have incorporated the list boxes to the Menu tab. The reason for this change is that all the combo boxes above 10 will be replaced by the two list boxes and the Search routine. Although I have learned how to populate one list box dependent upon another I do have a problem. The Topics populate all the Topics for the selected Category except the last one, regardless on the Category selected.

2. Once a Topic has been selected then the Goto Selection is enabled and the program would then point to the selected Topic like you were doing. That looks to be a major change. Clear?

3. You mentioned the building upon the Search routine so here's something to think on. Add an option to Find All that lists the Topics under the Search routine. The user than can select that Topic and go to it. Another big change and challenge.

4. The Topics are keyed on the worksheet tab name which can be unclear. Here's another opportunity. I would like to be able to change the Topic description to be longer than the tab name, yet go to the correct tab.

In the attached workbook I have not incorporated your Search form yet so please do not think it is not important - it is.

I'm not sure where to ask you too start if you accept working with me on these changes. I'll post individual questions for different changes. If you're interested and challenged by all this I would think that correcting the Topics list box that populates everything except but the last item would be a good starting point.

Interested in all this? Please advise
Thanks - Frank
Function-and-Formulas-for-Excel-.xlsm
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

Sure I'm interested and if you want to you can post #1 now.
ASKER
Frank Freese

Post is on its way
ASKER
Frank Freese

Martin
You ready for this:
The Topics are keyed on the worksheet tab name which can be unclear. Here's another opportunity. I would like to be able to change the Topic description to be longer than the tab name, yet go to the correct tab.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

Do you want to be able to see both the topic description and the longer description? If so I believe that can be done but about what would be the maximum length of the description?

What about the question you wanted the user to be asked each time before the GoToSelection button is activated? You were going to explain to me why that's necessary but of course you don't have to and I can just add the code to do that if you want.
ASKER
Frank Freese

I'm going back and forth on the Yes/No option. My first thought was to make sure the user selected the correct topic. The jury is still out on that but I respect your comment on how irritating that can be.

Regarding the topic description. I know it is tied to the worksheet name and there is a limitation on how much you can put in a tab. I will be going back and modifying the longer description later but the shorter description is unclear and that's the one I'd like to change.

I know others see my posts and I try to be fair in awarding points. I resist any bias because that would be unfair. But you're so in touch now with this project that my preference is to stay with you and give to you as many points as possible. You OK with that?

Some questions will be easy and others more time consuming but you are appreciated. I looked at your experience and I am so glad to see you stepping up to help others.  

I'll post tomorrow morning my next question. I take the "boss" out for dinner on Friday nights and our youngest son is also in a mini-marathon. He has asked us to be there to cheer him on. Have a great evening. I'm in Memphis are you close by any chance (probably Arizona or California :) ?
Martin Liss

I know others see my posts and I try to be fair in awarding points. I resist any bias because that would be unfair.
That's absolutely what you should do and I have no problem with that.

As far as modifying what the user sees in lstTopic (or lstCategory for that matter), whatever you put into the columns on the 'Topics' sheet will be reflected in the listbox with no detrimental effect of the code, so you maybe don't need to formally ask this question at all.

BTW I'm a retired programmer living in San Jose, CA.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Frank Freese

I knew you were retired, yet, thanks for hanging in there for us.
Let me try your suggestion, but still look for another question(s) regarding the Search function.
I bet you even remember punch cards.
ASKER
Frank Freese

I changed the description as you suggested and it appears to work OK! Good design here.
If I have any problems on that I'll ask a question
Martin Liss

I bet you even remember punch cards.
I do and I had desk drawers full of long but otherwise card shaped boxes that contained program code on Hollerith cards. The other programmers and I would draw a black market on the top edge of the cards from two diagonal corners in case, God forbid, we dropped the box. The procedure was

1.    Go punch a few hundred cards
2.    Put them in the box
3.    Mark the box
4.    Bring the box to the people who fed them into card readers
5.    Come back the next day and find out about the first error
6.    Punch a replacement card
7.    Repeat from Step 5 until the Analyst changed the requirements

Fun times:)

Back then at Xerox where I worked, there were still people "programming" by moving iron cores around the CPU with pliers. I go way back.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Frank Freese

Well you ole goat you've been around almost as long as I have been :)
I never was with a company or involved with moving iron cores but I've heard nightmares about it if you got it wrong. I do recall "bugs", and not in the program.

I started with punch cards at a university. If we did not like someone we had one additional step - Step 3a - remove a couple of cards, rearrange them slightly, or bump into the person so the box would drop to the ground. Needless to say those people either got out of the program (didn't belong anyway) or got with the program (quit cheating).

The Search question I'm submitting includes a worksheet that does not have the Search routine you have already built.  I thought it might be easier to simply incorpoarte the new routine all together. Adding to what you've done I'll be looking for this added feature - Search and Find All. Here the user can select through the results of the search routine for the specific word the going directly to the topic. I would like to search only coulmns B and C.

You'll notice that I removed all the command boxes I had planned to use since they won't be need. I really like the way you are managing the list boxes. Hopefully, I've challenged you on this project. I know I've learned a lot.

You're two hours behind me so I'll move on this around noon you're time (forgive me for stepping out this afternoon but Alabama plays A&M and down south that's a big game today. Tonight Ole Miss plays and this is Ole MIss country).
Martin Liss

One of the ideas I suggested a while back allowed the user to double click one of the rows on the sheet and go directly to the sheet described in the row. If you want that ability then post a new question and I'll be happy to add that functionality, but I was thinking about your project and IMO the rows don't serve much purpose, particularly if lstTopic was made to show a better description (as you said you were doing). If you don't need the rows then the current two listboxes could be put into a userform where they would behave better, and all the functions you want including Find, Find All, Exit Excel, etc could be run from buttons in that userform.
ASKER
Frank Freese

I can see the rows from 11 down going away. When a user selects a topic maybe a popup box appears with more description of that topic which I an now showing in Column C. The popup box would allow the user to GoTo the Selected Topic or Exit. This would elminate everything below row 10. Of course, if that look good then we would need to reconsider the Search function, since all those rows would be gone on tab Menu.

Your thoughts please before I post a question
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Martin Liss

You wouldn't need a popup box. The userform could contain a description box similar to the Request Detail box in this picture from another project I've been involved in.

pic
So post when you like and I or someone else will help.