Load images from separate folders

[kelly mort]

Hi,

This code is giving me a headache and I need someone fix it for me.

Currently both controls are loading the image from same folder "Picture1".

I want the second control to load from "picture2".

I have tried some stuffs but the script keeps laughing at me .

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Me.[h9] Or Target = Me.[h36] Then
    Select Case Target.Address
        Case Is = "$H$9"
            LoadIm "Image1", Me.[h9]
        Case Is = "$H$36"
            LoadIm "Image2", Me.[h36]
    End Select
End If
End Sub
Sub LoadIm(cname$, r As Range)
Dim fpath$, sfile$
[COLOR=#ff0000][B]fpath = ThisWorkbook.Path & "\Picture1"[/B][/COLOR]
sfile = Dir(fpath & "\" & Right(r.Text, 3) & ".*")
If sfile <> vbNullString Then
    Me.OLEObjects(cname).Object.Picture = LoadPicture(fpath & "\" & sfile)
Else
    Me.OLEObjects(cname).Object.Picture = LoadPicture("")
End If
If Err.Number = 53 Then Me.OLEObjects(cname).Object.Picture = LoadPicture("")
End Sub

 

[DanteAmor]

It can be the following

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Me.[h9] Or Target = Me.[h36] Then
        Select Case Target.Address
            Case Is = "$H$9"
                LoadIm "Image1", Me.[h9][COLOR=#0000ff], 1[/COLOR]
            Case Is = "$H$36"
                LoadIm "Image2", Me.[h36][COLOR=#0000ff], 2[/COLOR]
        End Select
    End If
End Sub


Sub LoadIm(cname$, r As Range,[COLOR=#0000ff] n As Long[/COLOR])
    Dim fpath$, sfile$
    fpath = ThisWorkbook.Path & [COLOR=#0000ff]"\Picture" & n[/COLOR]
    sfile = Dir(fpath & "\" & Right(r.Text, 3) & ".*")
    If sfile <> vbNullString Then
        Me.OLEObjects(cname).Object.Picture = LoadPicture(fpath & "\" & sfile)
    Else
        Me.OLEObjects(cname).Object.Picture = LoadPicture("")
    End If
    If Err.Number = 53 Then Me.OLEObjects(cname).Object.Picture = LoadPicture("")
End Sub

 

[kelly mort]

Cool !!!

But one thing is :

The real folders don't have such naming convention.

I never taught those sample I gave will trap me like this.

The folder names are
1. Passport
2. Signature

Sorry for not being clear at first

 

[DanteAmor]

Then send as parameter the full name

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Me.[h9] Or Target = Me.[h36] Then
        Select Case Target.Address
            Case Is = "$H$9"
                LoadIm "Image1", Me.[h9], [COLOR=#0000ff]"Passport"[/COLOR]
            Case Is = "$H$36"
                LoadIm "Image2", Me.[h36], [COLOR=#0000ff]"Signature[/COLOR]"
        End Select
    End If
End Sub




Sub LoadIm(cname$, r As Range, [COLOR=#0000ff]folder As String[/COLOR])
    Dim fpath$, sfile$
    fpath = ThisWorkbook.Path &[COLOR=#0000ff] "\" & folder[/COLOR]
    sfile = Dir(fpath & "\" & Right(r.Text, 3) & ".*")
    If sfile <> vbNullString Then
        Me.OLEObjects(cname).Object.Picture = LoadPicture(fpath & "\" & sfile)
    Else
        Me.OLEObjects(cname).Object.Picture = LoadPicture("")
    End If
    If Err.Number = 53 Then Me.OLEObjects(cname).Object.Picture = LoadPicture("")
End Sub

 

[kelly mort]

Then send as parameter the full name

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Me.[h9] Or Target = Me.[h36] Then
        Select Case Target.Address
            Case Is = "$H$9"
                LoadIm "Image1", Me.[h9], [COLOR=#0000ff]"Passport"[/COLOR]
            Case Is = "$H$36"
                LoadIm "Image2", Me.[h36], [COLOR=#0000ff]"Signature[/COLOR]"
        End Select
    End If
End Sub




Sub LoadIm(cname$, r As Range, [COLOR=#0000ff]folder As String[/COLOR])
    Dim fpath$, sfile$
    fpath = ThisWorkbook.Path &[COLOR=#0000ff] "\" & folder[/COLOR]
    sfile = Dir(fpath & "\" & Right(r.Text, 3) & ".*")
    If sfile <> vbNullString Then
        Me.OLEObjects(cname).Object.Picture = LoadPicture(fpath & "\" & sfile)
    Else
        Me.OLEObjects(cname).Object.Picture = LoadPicture("")
    End If
    If Err.Number = 53 Then Me.OLEObjects(cname).Object.Picture = LoadPicture("")
End Sub

Wow.

I can't believe this! !!

Can you please explain the logic behind the code for me?

Especially that folder thing.

 

[DanteAmor]

This routine receives 3 parameters:
Sub LoadIm (cname $, r As Range, As String folder)
The name of the form, the range and the name of the folder.


For example: LoadIm "Image1", Me. [H9], "Passport"


This time you are sending the data "Passport".


And in this line uses that data to complete the folder
fpath = ThisWorkbook.Path & "" & folder

 

[kelly mort]

Okay. Thanks for your patience.

 

[DanteAmor]

No problem. Let me know if you have any doubt.