upload pictures

[SCHMRO]

Hello,
This is my first VBA- project and I need a help

I have a userform with serveral textboxes
In the first of those textboxes, I put the varialel "NR_Agent" ( e.g. B1385, OR A6985) before clicking on my command button (Rechercher_Agent).
By clicking, the macro will automatically fill out the other text-and checkboxes. ( e.g. Name, Telefonsnumber etc...)
My code is working perfectly
Depending on the "NR_Agent", the macro is also uploading a picture ( Selfi that has been saved as "B1385 OR A6985") -> works also!

Here my problem:
I have +- 250 agents with an "agent-number" in my Excel, but I do not have picture from everyone. When I put the variable "NR_Agent" of an agent where I do not have a picture I get an error message " no input found"
Which is evident and normal, but I don't know how to write the code "if there is a picture -> upload it, if there is no picture -> continue"

Here is my code:

Private Sub Recherche_Agent_Form_Click()

Dim NR_Agent As String
NR_Agent =Trim(Userform1.Texbox1.Text)
lastrow = Worksheet("Sheet1").Cells(Rows.Count,1).End(xlUp).Row

For i = 3 To lastrow
If worksheets("Sheet1").Cells(i,1).Value = NR_Agent Then

Userform1.Textbox2.Text = Worksheet("Sheet1").Cells(i,2).Value
Userform1.Textbox3.Text = Worksheet("Sheet1").Cells(i,3).Value

Image1.Picture =LoadPicture("U:\Picture\" & Texbox1.Text & ".jpg")

Does anyone know how to do?

Thank you

 

[DanteAmor]

Welcome to the MrExcel Message Board!

In this way your code would be fixed.

Private Sub Recherche_Agent_Form_Click()
  Dim NR_Agent As String
  Dim lastrow As Long
  Dim i As Long
  Dim filename As String
  
  NR_Agent = Trim(UserForm1.TextBox1.Text)
  lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  filename = "U:\Picture\" & TextBox1.Text & ".jpg"
  
  For i = 3 To lastrow
    If Worksheets("Sheet1").Cells(i, 1).Value = NR_Agent Then
      UserForm1.TextBox2.Text = Sheets("Sheet1").Cells(i, 2).Value
      UserForm1.TextBox3.Text = Sheets("Sheet1").Cells(i, 3).Value
      If Dir(filename) <> "" Then
        Image1.Picture = LoadPicture(filename)
      End If
      Exit For
    End If
  Next
End Sub

But I guess you are looking for a single agent then it could be like this:

Private Sub Recherche_Agent_Form_Click()
  Dim f As Range
  Dim filename As String
  
  filename = "U:\Picture\" & TextBox1.Value & ".jpg"
  With Sheets("Sheet1")
    Set f = .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
        TextBox2.Value = .Cells(f.Row, 2).Value
        TextBox3.Value = .Cells(f.Row, 3).Value
        If Dir(filename) <> "" Then Image1.Picture = LoadPicture(filename)
    End If
  End With
End Sub

 

[SCHMRO]

Welcome to the MrExcel Message Board!

In this way your code would be fixed.

Private Sub Recherche_Agent_Form_Click()
  Dim NR_Agent As String
  Dim lastrow As Long
  Dim i As Long
  Dim filename As String
 
  NR_Agent = Trim(UserForm1.TextBox1.Text)
  lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  filename = "U:\Picture\" & TextBox1.Text & ".jpg"
 
  For i = 3 To lastrow
    If Worksheets("Sheet1").Cells(i, 1).Value = NR_Agent Then
      UserForm1.TextBox2.Text = Sheets("Sheet1").Cells(i, 2).Value
      UserForm1.TextBox3.Text = Sheets("Sheet1").Cells(i, 3).Value
      If Dir(filename) <> "" Then
        Image1.Picture = LoadPicture(filename)
      End If
      Exit For
    End If
  Next
End Sub

But I guess you are looking for a single agent then it could be like this:

Private Sub Recherche_Agent_Form_Click()
  Dim f As Range
  Dim filename As String
 
  filename = "U:\Picture\" & TextBox1.Value & ".jpg"
  With Sheets("Sheet1")
    Set f = .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
        TextBox2.Value = .Cells(f.Row, 2).Value
        TextBox3.Value = .Cells(f.Row, 3).Value
        If Dir(filename) <> "" Then Image1.Picture = LoadPicture(filename)
    End If
  End With
End Sub

Works Thank you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.