How to popup image on click in a cell [SOLVED]

[mani_bbc05]

From cell A1:A4 i have names of different fruits. Such as in cell A1 Apple, A2 Grapes. etc

I want a VBA code to popup the images of the respective fruits when clicked to their respective cells. The popup image should be in a userform. I dont want to use hyperlink or comment option.

 

[Luke M]

Several things you'll have to customize to suit, but the basic structure could be done this way. First, create your userform and insert an image block. SInce you just created it, I'll assume these are UserForm1 and Image1. Then, in the Sheet module, the code could be:

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Did user select a cell of interest?
Dim MyPath As String
Dim MyPic As String

'Where are the pictures stored?
MyPath = "C:My DocumentsMy pictures"

If Intersect(Target, Range("A1:A4")) Is Nothing Then Exit Sub
MyPic = Target.Value & ".jpg"

UserForm1.Image1.Picture = LoadPicture(MyPath & MyPic)
UserForm1.Show
End Sub

[/pre]
Note that you'll need appropriately named pictures in the folder specified. the LoadPicture method has additional properties that you may need to play with if you want a specific size image.

 

[mani_bbc05]

Thanks a lot Luke M for you quick response. your reply is of great help to me.


after seeing your code, i thought if it is possible to save the images in different sheets of the same workbook and then is it possible to pull the image from there for the popup in userform?


Also is it possible to save the activesheet in pdf or ppt format in the currently saved location of the file by using command button?


If you could attach the file with the code, then it would help me in a great way.

 

[Luke M]

No, if you must have a UserForm approach, the images will need to be stored in a drive. I suppose you could write a script to select the image in the worksheet, save to a temporary location on drive, then upload to UserForm, but it would get cumbersome.


My preferred method would be to go with using the comments...it's easy to setup, and people can hover over things to show/hide pics.


If you have Office 2007+, there is a built-in Save As pdf function available from the office button. There is not easy way to save to ppt...you'd have too figure out what images you want translated to which slides. For more info about saving as a pdf, I'd recommend either using the custom Google search in top-right of these pages to find similar threads, or a regular Google search to find other ideas.

 

[mani_bbc05]

Thanks Luke for the informations.