Link to home
Start Free TrialLog in
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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I'll have a demo in a couple of minutes.
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.
Avatar of 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.
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.
Next time you're in Memphis let me know. I'll treat you to some real BBQ
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
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 :)
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.
Let me finish the Search and I'll get back to you.
I looked at the Search and overall it looks good what you've done
I'll post a new workbook in an hour or so.
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.
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.
Want a different question?
Not right now. The Search function has been difficult but hopefully I'll be done with it today.
You've worked so hard on this. The next question will be a piece of cake. Thanks
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I'll look at it shortly
Sounds like you got yourself educated
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?
A wonderful solution
Great job!
Sure but I'm still open for ideas concerning the search for details.
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.
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.
Something like this?
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?
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
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.
no problem. let me know when you're back and we will pick up from there
Have a great and safe trip
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?
I'll give you the answer here if you like but I would of course prefer a new question.
New question on its way - now
I'm glad I was able to help you here.

Marty - MVP 2009 to 2013
I'm back a little early so if there's anything more you need just fire away.
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
Sure I'm interested and if you want to you can post #1 now.
Post is on its way
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.
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.
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 :) ?
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.
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.
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
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.
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).
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.
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
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.

User generated image
So post when you like and I or someone else will help.