Inserting pictures in UserForm from within workbook

[daniel_dani95]

I'm working on a multiple choice quiz for various subjects. Some of the questions have figures that go with them without which you can't solve the question. The problem I have is that i'm not sure how to specify picture path since it is intended to be sent to people who will download the file in various locations (which changes the specified path).

Now, my idea was to use worksheet which has all the figures listed with their names and then when excel generates random questions, if they have figure with them, VLOOKUP searches the table and brings back the picture. But I can't seem to be able to load the picture automatically into UserForm (as all the other operations are going in the background and only test form is visible to the user). I've tried with

Image1.Picture=Sheet1.Range("Z2").Value

and

Image1.Picture=Sheet1.Range("Z2").Picture

but nothing happens when i open the user form again, picture box is still empty.

Can anyone help with this as it's the only thing left to do before i have full working test

 

[John_w]

Now, my idea was to use worksheet which has all the figures listed with their names and then when excel generates random questions, if they have figure with them, VLOOKUP searches the table and brings back the picture.

That idea would work but you have to save the chosen picture as a local .jpg file and then use the native LoadPicture function to load the file to the userform image control.

Suppose all the pictures are on a sheet named "Pictures" and the result of the VLOOKUP in cell Z2 on the same sheet is the name of the picture to be loaded, the following userform code would load the picture to the image control. In this example I'm using a command button Click to show the picture.

Private Sub cmdShowPicture_Click()
  
    Dim picShape As Shape
    Dim tempImageFile As String
  
    With Worksheets("Pictures")
        Set picShape = .Shapes(.Range("Z2").Value)
    End With
  
    tempImageFile = Environ("temp") & "\image.jpg"
    Save_Object_As_Picture picShape, tempImageFile
  
    Me.Image1.Picture = LoadPicture(tempImageFile)
  
    Kill tempImageFile
      
End Sub


Private Sub Save_Object_As_Picture(saveObject As Object, imageFileName As String)

    'Save a picture of an object as a JPG/JPEG file
  
    'Arguments
    'saveObject    - any object type whose class has the CopyPicture method: Chart, ChartObject, ChartObjects, OLEObject, OLEObjects, Range, or Shape.
    'imageFileName - the .jpg or .jpeg file name (including folder path if required) the picture will be saved as.
  
    Dim temporaryChart As ChartObject
   
    Application.ScreenUpdating = False
    saveObject.CopyPicture xlScreen, xlPicture
    Set temporaryChart = ActiveSheet.ChartObjects.Add(0, 0, saveObject.Width, saveObject.Height)
    With temporaryChart
        .Activate
        .Chart.Paste
        .Chart.Export imageFileName
        .Delete
    End With
    Application.ScreenUpdating = True
    Set temporaryChart = Nothing
  
End Sub

 

[daniel_dani95]

That idea would work but you have to save the chosen picture as a local .jpg file and then use the native LoadPicture function to load the file to the userform image control.

Suppose all the pictures are on a sheet named "Pictures" and the result of the VLOOKUP in cell Z2 on the same sheet is the name of the picture to be loaded, the following userform code would load the picture to the image control. In this example I'm using a command button Click to show the picture.

Private Sub cmdShowPicture_Click()
 
    Dim picShape As Shape
    Dim tempImageFile As String
 
    With Worksheets("Pictures")
        Set picShape = .Shapes(.Range("Z2").Value)
    End With
 
    tempImageFile = Environ("temp") & "\image.jpg"
    Save_Object_As_Picture picShape, tempImageFile
 
    Me.Image1.Picture = LoadPicture(tempImageFile)
 
    Kill tempImageFile
     
End Sub


Private Sub Save_Object_As_Picture(saveObject As Object, imageFileName As String)

    'Save a picture of an object as a JPG/JPEG file
 
    'Arguments
    'saveObject    - any object type whose class has the CopyPicture method: Chart, ChartObject, ChartObjects, OLEObject, OLEObjects, Range, or Shape.
    'imageFileName - the .jpg or .jpeg file name (including folder path if required) the picture will be saved as.
 
    Dim temporaryChart As ChartObject
  
    Application.ScreenUpdating = False
    saveObject.CopyPicture xlScreen, xlPicture
    Set temporaryChart = ActiveSheet.ChartObjects.Add(0, 0, saveObject.Width, saveObject.Height)
    With temporaryChart
        .Activate
        .Chart.Paste
        .Chart.Export imageFileName
        .Delete
    End With
    Application.ScreenUpdating = True
    Set temporaryChart = Nothing
 
End Sub

My Z2 Cell gives the picture itself, i presume that would create a problem with the code you wrote. Should I fix that and put just the figure name into Z2 cell and then try with this?

 

[Micron]

Maybe something like what's suggested here (note thread title).

Show images from worsheets to UserForm

Hi guys I've got a project but I don't know how to solve this: I want to show an image that already exists in my worksheet using the Image control from UserForm but all the methods that I have seen use the path of the file to insert the image. I can't use this method since I have to share my...

www.mrexcel.com

 

[daniel_dani95]

Maybe something like what's suggested here (note thread title).

Show images from worsheets to UserForm

Hi guys I've got a project but I don't know how to solve this: I want to show an image that already exists in my worksheet using the Image control from UserForm but all the methods that I have seen use the path of the file to insert the image. I can't use this method since I have to share my...

www.mrexcel.com

my problem is that pictures shoud be loaded with questions, so sometimes question 1 will have figure 1, sometimes figure 31 and sometimes no figure

 

[Micron]

sometimes question 1 will have figure 1, sometimes figure 31 and sometimes no figure

If you're saying that sometimes the same question uses different pics then that would require references that tie pics to question, similar to how relational databases work. However, you would also need a means of choosing from the pics related to a question by random means if there is more than one?

If you're saying one time #1 is question A and another time #1 is question B then those numbers are just list numbers. They don't have any relationship to a particular question so that is a fundamental design problem I'd say. If you have 50 questions, each one should have an id (1 thru 50 if you prefer) and each number should have **a related pic**. When you present the list, the list numbers are only for ordering the questions, nothing else.

** - or if more than one, again, you'd need a sub list for pics that are for the same exact question and a way to choose one or the other.
HTH

 

[daniel_dani95]

If you're saying that sometimes the same question uses different pics then that would require references that tie pics to question, similar to how relational databases work. However, you would also need a means of choosing from the pics related to a question by random means if there is more than one?

If you're saying one time #1 is question A and another time #1 is question B then those numbers are just list numbers. They don't have any relationship to a particular question so that is a fundamental design problem I'd say. If you have 50 questions, each one should have an id (1 thru 50 if you prefer) and each number should have **a related pic**. When you present the list, the list numbers are only for ordering the questions, nothing else.

** - or if more than one, again, you'd need a sub list for pics that are for the same exact question and a way to choose one or the other.
HTH

There is a question bank loaded in excel worksheet (let's call it TestSheet), in Testsheet there is question column, answers column and figure column. Another sheet (let's call it RandomSheet) then picks random numbers within question bank range (let's say there's 100 questions in bank and test has 30) and displays those questions on the UserForm. That means that sometimes first in UserForm will be random question between 1 and 100 in TestSheet.

I think i will just make all the variations with formula from other thread

 

[John_w]

Now, my idea was to use worksheet which has all the figures listed with their names and then when excel generates random questions, if they have figure with them, VLOOKUP searches the table and brings back the picture.

What is the layout of the table that VLOOKUP is searching?

My Z2 Cell gives the picture itself, i presume that would create a problem with the code you wrote.

Yes, my code wouldn't work with that setup. How is the picture being put in Z2? What is the code?

Should I fix that and put just the figure name into Z2 cell and then try with this?

If you have the named pictures on a sheet, a table of values and picture names and a VLOOKUP then my code should work. Something like this, with my VLOOKUP in D15 (so the Range("Z2") in my code should be changed to Range("D15"))

Userform VLOOKUP show image from sheet.xlsm
	A	B	C	D
11	Pictures lookup table
12	Value	Picture Name		Picture Value
13	1	Pic_00001		3
14	2	Pic_00002
15	3	Pic_00003		Pic_00003
16	4	Pic_00004
17	5	Pic_00005
Pictures

Cell Formulas
Range	Formula
D15	D15	=VLOOKUP(D13,Table1[#All],2)