Import Userform Image dependant on Textbox input

[atmospheric]

Hi, I have a VERY simple working Userform that looks up the first of three columns (of a Victorian postmark database, in Sheet "England") and populates the remaining two Textboxes after manually inputting TextBox1 (a number).

So, for example, if I typed 123 into Textbox 1, Textboxes 2 and 3 would fill in with the Town and City that used the numeric Postmark "123"

I would like to have the Userform display an associated image in the Imagebox below the Textboxes.

The images would be stored in Sheet1 and would have the same name, e.g. if the postmark was 123, the associated jpeg image would be named 123.

My code so far:

Private Sub TextBox1_Change()


ROW_NUMBER = 41

Do
DoEvents
ROW_NUMBER = ROW_NUMBER + 1
item_in_review = Sheets("England").Range("B" & ROW_NUMBER)
If item_in_review = TextBox1.Text Then
TextBox2.Text = Sheets("England").Range("C" & ROW_NUMBER)
TextBox3.Text = Sheets("England").Range("D" & ROW_NUMBER)

End If

Loop Until item_in_review = ""
End Sub

I have searched here and on the internet, but not found exactly what I'm after, so any help would be appreciated. TIA

 

[MickG]

Its a bit complicated to place Images from a sheet into the "Userform", but it can be done by using a chart Object to save the sheet picture as a "jpg" file and then inserting it into an "Image" control.
Are you still interested ???

 

[diddi]

keep a library of images in a folder and load them up as needed. smaller file and prolly quicker.

 

[atmospheric]

Hi MickG and diddi. I intend to pass the finished work around so I can't keep the images on C: somewhere, hence the idea of keeping them in one of the sheets.

I'm open to any suggestions that achieve the end result, and if the Chart Object route is the best way, please advise further!

Many thanks,

 

[MickG]

Assuming your pictures are on sheet1 then you will need to place a chart object the same size as the pictures on the sheet.

The code assumes that the each picture name has 3 numbers like:- 123, 124, 125,etc
When you Open the "Userform" and run the code below:-
On opening the Userform and entering the picture name required in "Textbox1", then if the picture exists the code will place the sheet Picture in the chart object and Export it as a "Temp.jpg" file to the Current WorkBook Path. If not a Msgbox will appear !!

If the Picture Exists the picture will show in Image1 control of the "Userform".


Entering another number will replace the existing "jpg" file with another containing the new Picture, which should then show in the Image control.


Userform:- TextBox1.code:-

Private [COLOR=navy]Sub[/COLOR] TextBox1_Change()
[COLOR=navy]Dim[/COLOR] fname [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] CurrentChart [COLOR=navy]As[/COLOR] Chart
[COLOR=navy]Dim[/COLOR] Pic [COLOR=navy]As[/COLOR] Shape
[COLOR=navy]Dim[/COLOR] Fd [COLOR=navy]As[/COLOR] Boolean
[COLOR=navy]If[/COLOR] Len(TextBox1.Text) = 3 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Pic [COLOR=navy]In[/COLOR] Sheets("Sheet1").Shapes
        [COLOR=navy]If[/COLOR] Pic.Name = TextBox1.Text [COLOR=navy]Then[/COLOR]
            Fd = True
            [COLOR=navy]Exit[/COLOR] For
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Pic
[COLOR=navy]If[/COLOR] Fd [COLOR=navy]Then[/COLOR]
    [COLOR=navy]With[/COLOR] Sheets("Sheet1")
            .Shapes(TextBox1.Text).Copy
            [COLOR=navy]Set[/COLOR] CurrentChart = .ChartObjects(1).Chart
            CurrentChart.Paste
            
            fname = ThisWorkbook.Path & "\temp.jpg"
            CurrentChart.Export Filename:=fname, filtername:="jpg"
            CurrentChart.Shapes.SelectAll
            Selection.Delete
            fname = ThisWorkbook.Path & "\Temp.jpg"
            Image1.Picture = LoadPicture(fname)
  [COLOR=navy]End[/COLOR] With
[COLOR=navy]Else[/COLOR]
    MsgBox "Picture does not exist"
[COLOR=navy]End[/COLOR] If
   
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[atmospheric]

Hi MickG, I struggled with inserting the ChartObject but meanwhile a friend has offered an alternative solution.

Place all the Images in a folder and use the following code:


Private Sub TextBox1_Change()
On Error Resume Next

ROW_NUMBER = 41

Do
DoEvents
ROW_NUMBER = ROW_NUMBER + 1
item_in_review = Sheets("England").Range("B" & ROW_NUMBER)



If item_in_review = TextBox1.Text Then
TextBox2.Text = Sheets("England").Range("C" & ROW_NUMBER)
TextBox3.Text = Sheets("England").Range("D" & ROW_NUMBER)
Sheets("England").Select
Let x = item_in_review
strPath = ActiveWorkbook.Path
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
If x = "" Then Let x = 1000000

Me.Image.Picture = LoadPicture(strPath & x & ".jpg")
End If

End If

Loop Until item_in_review = ""
b:
Let x = 1000000
' resume Next

End Sub

/Code

The images appear in the ImageBox as desired, but there needs to be a specific image "1000000.jpg" which displays "Image not Found" in the folder with all the other images.

Thanks for your help in this matter, much appreciated.

 

[MickG]

This works for me !!!

Private [COLOR="Navy"]Sub[/COLOR] TextBox1_Change()
 [COLOR="Navy"]Dim[/COLOR] ROW_Number [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] item_in_review [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 [COLOR="Navy"]Dim[/COLOR] StrPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 StrPath = ActiveWorkbook.Path
 ROW_Number = 41
    
    [COLOR="Navy"]If[/COLOR] Len(TextBox1.Text) = 3 [COLOR="Navy"]Then[/COLOR]
         Do
            ROW_Number = ROW_Number + 1
            item_in_review = Sheets("England").Range("B" & ROW_Number)
            [COLOR="Navy"]If[/COLOR] item_in_review = TextBox1.Text [COLOR="Navy"]Then[/COLOR]
                    TextBox2.Text = Sheets("England").Range("C" & ROW_Number)
                    TextBox3.Text = Sheets("England").Range("D" & ROW_Number)
                    Me.Image1.Picture = LoadPicture(StrPath & "\" & item_in_review & ".jpg")
            [COLOR="Navy"]End[/COLOR] If
        Loop Until item_in_review = ""
    [COLOR="Navy"]ElseIf[/COLOR] item_in_review = "" [COLOR="Navy"]Then[/COLOR]
        item_in_review = 1000000
        Me.Image1.Picture = LoadPicture(StrPath & "/" & item_in_review & ".jpg")
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick