Start Free Trial

asked on

Ability to search a workbook for a product. VB Code?

Hi there,

I have an Excel Workbook that contains a number sheets with thousands of products. The workbook has been designed for easy navigation using VB controls etc. I am wanting to add a search feature on the main page for product code or product type.

I'm sure it is possible, could somebody please help?

Basically, at the moment the user selects the brand using a button on the main page. then a product type from the next sheet. Up comes a sheet with a list of products including Product ID, Product Type code, Description, Price etc. I have an auto filter enabled also. this is the case for each sheet containing the products.

the search feature should allow you to enter 2 options only. Product Type code and Product ID. Product ID's are unique, product Type Codes are not. Each product Type code could have one or many different products related to it but will always be on the same sheet.

Your assistance would be greatly appreciated.

Does each sheet contain only one product type code.

Explain a bit more. Do you want user to enter product type code and product id then the search feature will find the sheet that contains the product type code or does the search create a new sheet, search all the other sheets for product type code and insert into the new sheet. Just explain a bit more of the process and I may be able to help

ASKER

Excellent! Thank you... I will do my best to explain it to you...

>Does each sheet contain only one product type code< = No - Each sheet has various product type codes. but will not repeat in other sheets.

For example; Someone who knows the products back to front will click on the Valve category button required (main page) which will take you to the respective sheet.

They will then either:

- Select the PRODUCT TYPE CODE using the auto filter drop down list and it would leave a list of that particular PRODUCT TYPE containing 1 or more PRODUCT ID's (usually more...). They then select the Product ID required (highlighting the row) to provide customers with whatever details they require such as price etc...

OR

- select the PRODUCT ID Directly using the auto filter drop down list leaving only the ONE product listed.

If we have someone that knows..:

- the PRODUCT TYPE CODE only, we would like for them to simply enter that code in Search Field 1 on the main page and it should take them to the correct sheet and display a list of that particular PRODUCT TYPE (They will then decide which PRODUCT ID is required depending on price or specs etc...)

- the exact PRODUCT ID, we would like for them to simply enter that code in Search Field 2 on the main page and it should take them to the correct sheet leaving only the ONE product listed.

Could I e-mail you the existinf file? I will have to modify it to exclude confidential information but it would definately explain itself if I have made the above sound too confusing...

Thank you SO much for your time... It is greatly appreciated!!!

Yeah it would help a lot seeing the workbook definitely and I would like to help but I remember reading in the EE guidelines that we are not allowed to give emails because it is not fair on other experts. I will give you my email if you get a permission from a moderator. Get a moderator to put a comment here that it ok to exchange emails. I mean there are no other experts participating now.

Sorry I am just being careful I don't want my account to be suspended

ASKER

I have posted the following. I hope they allow it... :)

https://www.experts-exchange.com/questions/21398250/Working-exchange-e-mail-address-with-an-Expert.html

ASKER

I have uploaded the file to http://www.uctrlit.com/gbzhhu.xls

Thank you

Oh Good. I'll have a look at it now

I don't know how you upload these things but I can only open it in IE. Could you upload such that I can select save instead of it opening in IE automatically

Cheers

Cheers N

I tried to save it locally which was Ok but when I opened in Excel it still had IE interface and acted odd. If I clikced a button to go to another sheet it will say the sheet has to be opened in a seperate window

burkertadmin,

Could you try to zip it up as suggested by N

Ta

Netminder,

Sorry this is off topic and I am being cheeky. I am qualified (way over) to get a free premium and I already have a paying premium membership which is to be billed again in May 29. What can I do to avoid the billing in May. I am also moving to a new job where they are very busy and I am unlikely to get enough time to participate as much as I do now

Thanks

Cheers Netminder. I am definately gonna click on the You Qualify link now

Netminder,

Just clicked the link and it is all praise for us experts until I got to the last paragraph woith this warning

WARNING: You are subscribed to a long term Premium Services subscription. Upgrading to Qualified Expert Premium Services will REPLACE your current long term subscription. You will lose any remaining time in your current subscription. Unless your current subscription is about to expire, upgrading to Qualified Expert Premium Services is NOT recommended.

It ;looks like I am going to lose 1 month already paid for so I didn't go along with the upgrade. I will see what I can do at the end of May. It would have been much nicer to have the paid month stored for when I need it. perhaps you could raise this with the EE admin

Thanks

ASKER

I have zipped the file. Please let me know if you receive it OK..

http://www.uctrlit.com/gbzhhu.zip

ASKER

You can also try right clicking the link above and selecting 'Save Target As...'

Yep, I now have the file. I'll see what I can do. I have only got today and tomorrow(I am leaving this job and starting another in 10 days time) If I can't do what you want by tomorrow please get other experts involved here as I won't be able to participate for quite awhile

jansuper,

Isn't there a way that you can store the info for experts that have QEPS and paying PS so that they can mix and match the two systems. I.e I have 2 PS months left then I qualify for QEPS, the 2 months stay until I fail to qualify QEPS at which point the 2 month PS kicks in

I would say it is a little unfair for me to have to use my PS month in May although I had over 26000 expert points this month

Hi

I can find PRODUCT ID for the detail sheets but where is the PRODUCT TYPE CODE. Is it the info on the Welcome sheet or the sub sheets?

Need to reply quicker than this so we can get a solution by tomorrow.

The way it looks to me:

There are no association between Product types and sheets meaning there is no easy way to tell what sheet contains a given product type without searching, so here I am suggesting that we add a function to one of the modules that returns true or false given a sheet and a product type we could call it IsProductInSheet(Prod as string, sh as sheet). I will do this as soon as you let me know where the product type codes are displayed in each sheet. I don't want to do guess work as it maybe a waste of time

User will type a product type code, we call our function to check which sheet contains that product type code
Once that function returns true we then display that sheet. User will select Product ID from the list

A few questions?

Are product type codes in the same column on every sheet?
Are product IDs in the same column on every sheet?
Which sheets contain Product Type Codes?
Which sheets contain Product IDs?

There are too many sheets for me to figure it out, it is easier for you I guess as you are familiar with the system

Cheers

Ok I have it working with Product type codes. I am just searching the whole workbook with a sheet that contains what user entered. We can change this when we know the columns th eproduct type codes are in

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Oh! forgot to say that I couldn't upload changed code because I am editing it in Excel 97 which said when I tried to save that some formatting will be lost since workbook was created with a newer Excel version. However as you see the code changes are only in two places and it is easy to just paste and go. The interface design is better that you do it anyway as you know how you want it to look

Cheers mate

G'day

ASKER

WOW!!! I will have to test it out tomorrow. I have been checking my emails throughout the weekend and didn't see these until now. It's 3:20AM here in Australia. We also had a long weekend... Anzac Day... Where are you located?

I appreciate your help soooooo much. I will let you know how I go... I know it's going to work! (Hense the accepted answer!)

THANK YOU x 10,000,000,000,000

You are very welcome

I am in England.

Remember tomorrow's my last day here, so let's test it out and finish it off tomorrow morning. Afternoon I'll be digging into my wallet buying leaving drinks for my colleague (soon to be ex colleagues!)

Thanks for the points and the grade

Thanks Netminder. I don't know the email address I'll have in my new job. Once I start the job I will write to you to help me update my profile.

And also just heard from Jan Louwerens. She increased my premium services by 3 months and I am very pleased

Thanks you guys

As soon as posted it I regretted because we can't assume gender by name. Apologies to Jan. What is it short for?

The most important point is that I can now see that my efforts are appreciated and rewarded more than I expected. May EE live very long

There I go again. I better zip it up before I ask anymore stupid Q's. Frankly I am from a non English speaking country and the names can confuse me. My best mate is called Sandy .. a he too!!

ASKER

Hi there....

It all works very very well. Thanx again!

I have a couple of questions which after reading the EE gudelines I assumed I need to start another question. Please see https://www.experts-exchange.com/questions/21403561/Tweaking-search-for-lazy-users-Excel-VB-search.html as I would prefer to give you the points...

Cheers mate!