# How to Write a VBA for Product Search and Button Location Change



## KCTOH (Dec 13, 2022)

a code for the button query that finds the product and displays the detail along with the photo (pID) name as seen in the query sheet.

a button change location code


----------



## tj4242 (Dec 14, 2022)

This searches the Query tab and fills in most of the table.  

There are some gotcha and simplifications.  

The Contains and Equal To are taken literally.  bCode searches for contains using InStr.   Brand has to be an exact match.  I did not build in a way to change those.
There is no logic if multiple search criteria are given
There is no code to display a picture
I was not sure what the Change Location button should do.  So it is unimplemented.
You need to create the 2 buttons (Clear and query) and link to the 2 subs with click.
All fun things for you to enhance but the basics are there to start.

Product Search.xlsmABCDEFG1Product Locator Updated23bCodeContains4BrandEqual To5DescriptionContains6pCodeContains7CategoryEqual To89ImageProductLocationChange1011WH12LN13RK14LV15BN16Sheet1

There must be a sheet named Query with the data
Product Search.xlsmABCDEFGHIJKLMNO1234pIDpCodebCodesBrandCategorysDescriptionWarehouseLaneRackRackLevelBinHQHDSupplierImage51205EB005602644020415EARTHBATHDOG SHAMPOOTEA TREE & ALOE VERA SHP 472ml324445100045ABC62869O/BCC5000744845401058OXBOWSA DFESSENTIALS GUINEA PIC FOOD3241030369YTR73368PB5BULPB5BULBURPDOG TREATS(NS) PB 5" (50pcs)32314140078VBM8462584553118853301140652WHISKASCAT DRY FDMACKEREL POCKET 1.2kg315122500400YTU917663CD0428887677760121ARISTO CCAT CAN FDPREMIUM TUNA SMOKED FISH 80g322330320KLO10Query


```
'Constants with column number from Query worksheet

Const bCode As Integer = 3
Const Brand As Integer = 4
Const Description As Integer = 6
Const pCode As Integer = 2
Const Category As Integer = 5
Const HQ As Integer = 12
Const HD As Integer = 13
Const Supplier As Integer = 14
Const WH As Integer = 7
Const LN As Integer = 8
Const RK As Integer = 9
Const LV As Integer = 10
Const BN As Integer = 11



Sub clear_Button1_Click()
    Dim i As Integer
    
    'Button says to just clear C3 to C7
    For i = 3 To 7
        Cells(i, 3) = ""
    Next
    
    'But let's clear the results too
    Cells(16, 1) = ""
    Cells(11, 2) = ""
    Cells(12, 2) = ""
    Cells(14, 2) = ""
    Cells(16, 2) = ""
    Cells(11, 6) = ""
    Cells(12, 6) = ""
    Cells(13, 6) = ""
    Cells(14, 6) = ""
    Cells(15, 6) = ""
    
End Sub

Sub Query_Button2_Click()

    Dim query_row As Double
    Dim found As Boolean
    
    found = False
    query_row = 5
    
    While ((Worksheets("Query").Cells(query_row, 1) <> "") And (found = False))
            
        'if bCode contains
        If Cells(3, 3) <> "" Then       'if cell is empty, InStr returns start
            If (InStr(Worksheets("Query").Cells(query_row, bCode), Cells(3, 3)) > 0) Then
                fill_in_chart (query_row)
                found = True
            End If
        End If
        
        'if Brand is equal to
        If Worksheets("Query").Cells(query_row, Brand) = Cells(4, 3) Then
            fill_in_chart (query_row)
            found = True
        End If
        
        'if Description contains
        If Cells(5, 3) <> "" Then       'if cell is empty, InStr returns start
           If (InStr(Worksheets("Query").Cells(query_row, Description), Cells(5, 3)) > 0) Then
               fill_in_chart (query_row)
               found = True
           End If
        End If

        'if pCode contains
        If Cells(6, 3) <> "" Then       'if cell is empty, InStr returns start
            If (InStr(Worksheets("Query").Cells(query_row, pCode), Cells(6, 3)) > 0) Then
                fill_in_chart (query_row)
                found = True
            End If
        End If
        
        'if Category is equal to
        If Worksheets("Query").Cells(query_row, Category) = Cells(7, 3) Then
            fill_in_chart (query_row)
            found = True
        End If
        
        query_row = query_row + 1
    Wend
 End Sub
 
Sub fill_in_chart(query_row)
    Cells(16, 1) = Worksheets("Query").Cells(query_row, HQ) & " ... " & Worksheets("Query").Cells(query_row, HD)
    Cells(11, 2) = Worksheets("Query").Cells(query_row, Brand) & " ... " & Worksheets("Query").Cells(query_row, Category)
    Cells(12, 2) = Worksheets("Query").Cells(query_row, bCode) & " ... " & Worksheets("Query").Cells(query_row, pCode)
    Cells(14, 2) = Worksheets("Query").Cells(query_row, Description)
    Cells(16, 2) = Worksheets("Query").Cells(query_row, Supplier)
    Cells(11, 6) = Worksheets("Query").Cells(query_row, WH)
    Cells(12, 6) = Worksheets("Query").Cells(query_row, LN)
    Cells(13, 6) = Worksheets("Query").Cells(query_row, RK)
    Cells(14, 6) = Worksheets("Query").Cells(query_row, LV)
    Cells(15, 6) = Worksheets("Query").Cells(query_row, BN)
 End Sub
```


----------



## KCTOH (Dec 15, 2022)

shorturl.at/lvwR7


----------



## KCTOH (Dec 15, 2022)

The fill_in_chart(query_row) not show on the file


----------



## KCTOH (Dec 16, 2022)

tj4242 said:


> This searches the Query tab and fills in most of the table.
> 
> There are some gotcha and simplifications.
> 
> ...


Dear ,
tj4242​I have change Query to MasterData, and now the Data show when search. as below 1111.JPG

Button Change location was like a changes the Data at MasterData
    Cells(11, 6) = Worksheets("MasterData").Cells(query_row, WH)
    Cells(12, 6) = Worksheets("MasterData").Cells(query_row, LN)
    Cells(13, 6) = Worksheets("MasterData").Cells(query_row, RK)
    Cells(14, 6) = Worksheets("MasterData").Cells(query_row, LV)
    Cells(15, 6) = Worksheets("MasterData").Cells(query_row, BN)
example : WH32 - LN4 - RK10 - LV3 - BN 0 changes to WH32 - LN5 - RK12 - LV3, Data was save 

How to get the code to display a photo at the proper size 5 x 5cm Row 10-15 Column A. The image with the ID 2869 is stored in the folder C:\Users\New folder\2869.


----------



## tj4242 (Dec 16, 2022)

Sorry.  I have never done anything with photos in Excel.


----------



## KCTOH (Dec 17, 2022)

tj4242 said:


> Sorry.  I have never done anything with photos in Excel.


how to get the photo from the folder follow with the pID picture name


----------

