[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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.  
0
Fernando
Asked:
Fernando
  • 17
  • 7
1 Solution
 
gbzhhuCommented:
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
0
 
FernandoAuthor Commented:
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!!!
0
 
gbzhhuCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

http://www.experts-exchange.com/Community_Support/Q_21398250.html
0
 
FernandoAuthor Commented:
I have uploaded the file to http://www.uctrlit.com/gbzhhu.xls

Thank you
0
 
gbzhhuCommented:
Oh Good.  I'll have a look at it now
0
 
gbzhhuCommented:
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
0
 
gbzhhuCommented:
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

0
 
gbzhhuCommented:
Cheers Netminder.  I am definately gonna click on the You Qualify link now
0
 
gbzhhuCommented:
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
0
 
FernandoAuthor Commented:
I have zipped the file. Please let me know if you receive it OK..

http://www.uctrlit.com/gbzhhu.zip
0
 
FernandoAuthor Commented:
You can also try right clicking the link above and selecting 'Save Target As...'
0
 
gbzhhuCommented:
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  

0
 
gbzhhuCommented:
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?
0
 
gbzhhuCommented:
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

0
 
gbzhhuCommented:
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
0
 
gbzhhuCommented:
Ok I went with the guess work as I haven't heard from you for awhile

Add this to one of your modules

Public Sub GetSearchItem(Prod As String, Optional ProdID As Boolean = False)
    Dim sh As Worksheet
    Dim bItemFound As Boolean
   
    Application.ScreenUpdating = False
   
    For Each sh In Worksheets
        If DisplaySearchItem(Prod, sh, ProdID) Then
            bItemFound = True
            Exit For
        Else
            bItemFound = False
        End If
    Next sh

    If bItemFound <> True Then
        MsgBox "Item " & Prod & " not found"
    End If
   
    Application.ScreenUpdating = True
End Sub

Public Function DisplaySearchItem(Prod As String, aSheet As Worksheet, Optional ProdID As Boolean = False) As Boolean
    Dim c As Object
    Dim c2 As Object
   
    aSheet.Select
    Range("A1").Select
    Set c = Cells.Find(What:=Prod, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)
   
    If c Is Nothing Then
        DisplaySearchItem = False
    Else
        c.Activate
        Selection.AutoFilter Field:=c.Column, Criteria1:="=" & Prod
        DisplaySearchItem = True
    End If
End Function

==========================================

In the Welcome sheet (Main sheet) add two textboxes (textbox1 for Product Type Code entry and TextBox2 for Product ID entry)
Add A Command Button for user to click o do the search.  Now add the following code for the command button

Private Sub CommandButton9_Click()
    If TextBox2.Text <> "" Then
        GetSearchItem TextBox2.Text, True
    Else
        If TextBox1.Text <> "" Then
            GetSearchItem TextBox1.Text
        End If
    End If
End Sub

It may not be Command button 9 if you added other command buttons since you sent me the workbook

That is it!  If you type text into TextBox1 and click Search command button you will be taken to the sheet that contains the product code (if there is one)

If you type text into TextBox2 and click Search command button you will be taken to the sheet that contains the product ID (if there is one) and only that product ID will displayed

If search tem is not found a message box will be displayed saying so

Hope that is what you wanted





0
 
gbzhhuCommented:
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
0
 
FernandoAuthor Commented:
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
0
 
gbzhhuCommented:
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
0
 
gbzhhuCommented:
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
0
 
gbzhhuCommented:
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
0
 
gbzhhuCommented:
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!!
0
 
FernandoAuthor Commented:
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 http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21403561.html as I would prefer to give you the points...

Cheers mate!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now