I need to move next / previous after search a record

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Dear all, I have been headache for this 2 functions for many days already. I hope someone can help me to solve my problem.
I have a search button which allow me to search record by using product_id, and display the picture. Then, i wish to move next / previous start from the searched record. I have tried many codes but not work. all move start from first record. 😭😭😭

So far the search and show picture work good for me... but i dunno how to write the code for move next / previous....
the search code i get from online, i tried can use then i just use it. if you want to help me to modify also can, i will really appreciate that.

Additional to that: in my showPic() function, if no picture found, i want to display a default picture, how should i do it?

Rich (BB code):
Private Sub cmdSearch_Click()
    Dim rngID As Range

    Set rngID = Sheets("Main").Range("B:B").Find(txtID, , xlValues, xlWhole, 1, 1, 0)
    
    If Not rngID Is Nothing Then
        txtCategory.Text = rngID.Offset(0, -1).Value
        txtName.Text = rngID.Offset(0, 1).Value
        txtStock.Text = rngID.Offset(0, 2).Value
        txtPrice.Text = rngID.Offset(0, 3).Value
        txtType1.Text = rngID.Offset(0, 4).Value
        txtType2.Text = rngID.Offset(0, 5).Value
        
        txtID.Tag = rngID.Address  'store the location of the matched ProductID
        Call showPic
    Else
        MsgBox StrConv(txtID.Text, vbUpperCase), vbExclamation, "No Match Found"
    End If  

End Sub

Private Sub showPic()
    Dim **** As String   
    **** = ThisWorkbook.Path & "\" & "AP_Folder" & "\" & txtID.Value & ".jpg"
        
    If Dir(****) <> "" Then
        Image1.Picture = LoadPicture(****)
    Else
        Image1.Picture = LoadPicture(none)
    End If

End Sub
 

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I got another question again...

I add in a listbox into my userform, when I search by using name (eg. Cola), all product name with "cola" will be displayed.
When I click on the PID, the image1 will display the photo according to PID.

But now, when I click the Clear button to set all fields = "", the listbox_click got error. Run-time error '1004': Application-defined or object defined error.
yellow color highlighted here==> Set iFound = .Find(lstSearchResults.Value)

How can I correct this problem?

Rich (BB code):
Private Sub lstSearchResults_Click()
 
    Dim iFound As Range
    Dim fPath As String
    
    With Range("Product_ID")
        Set iFound = .Find(lstSearchResults.Value)
    On Error Resume Next
        
        If iFound Is Nothing Then
            Image1.Picture = LoadPicture(fPath & "nopic.gif")
        Else
            With iFound
                'Look in the directory where this workbook is located.
                fPath = ThisWorkbook.Path & "\" & "AP_Folder" & "\"
                On Error Resume Next
                'If a matching picture is found then display it.
                Image1.Picture = LoadPicture(fPath & lstSearchResults.Value & ".jpg")
                'If No picture found then display the default picture.
                If Err = 0 Then Exit Sub
                Image1.Picture = LoadPicture(fPath & "nopic.gif")
            End With
        End If
    End With
    
    Set iFound = Nothing

End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,981
I get inconsistant results using the .Value property of a list box, the .Text property is more reliable.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,780
Members
416,939
Latest member
Rajakumaran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top