Import Userform Image dependant on Textbox input

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565
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).

1YBbnEb.jpg


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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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 ???
 
Upvote 0
keep a library of images in a folder and load them up as needed. smaller file and prolly quicker.
 
Upvote 0
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,
 
Upvote 0
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:-
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
This works for me !!!

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top