?
Solved

Insert Picture based on hyperlink/file location from another sheet in an Excel workbook

Posted on 2014-07-14
9
Medium Priority
?
1,075 Views
Last Modified: 2014-10-13
I am wanting to do something that seems simple but i am unable to find any solutions on the internet so it may not even be possible. I have an excel sheet that contains a list of hyperlinks for pictures. For Example, cell C2 would have a hyperlink, C3 would have a hyperlink, etc etc. We have another sheet in the same workbook that we want to show the picture from the sheet that has the hyperlinks. The theory behind this is we want someone to look at the sheet of picture data that has the hyperlinks, and the other sheet actually show the pictures so we can print it for a photo log in a report. Right now we are creating the hyperlinks and then manually having to Insert each Picture into each cell for the photo log. Since i already have the hyperlink to the picture, is there a way to tell a cell to show the picture based on the hyperlink, instead of having to manually go to Insert, Picture.

I can sort of do what I am wanting using vba and a image active X control but it is not automatically updating the pictures. It also will not automatically change the pictures when i update the data path in the cell. For example, I have the image path saved in cell A1 and A2. I have 2 different image controls. A1 is set to imagecontrol 1 and A2 is set to Imagecontrol 2. I can get the pics to show up but if i make any changes to A1 and A2, the pics do not change. This is the example I have of my code.

Image2.Picture = LoadPicture(Range("A1").Value)

I have placed it in _click(), _GotFocus(), but neither places will allow it to change and update automatically.
0
Comment
Question by:thirdrockit
  • 4
  • 3
8 Comments
 
LVL 4

Accepted Solution

by:
Scott Craig earned 2000 total points
ID: 40194876
You could issue a command upon the cell changing.  For example:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("C2")) Is Nothing Then Macro
End Sub

Open in new window


You can use Me.Range("H5"), if the event handler is on the code page for the worksheet in question (it usually is):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("C2")) Is Nothing Then Macro
End Sub

Open in new window

0
 

Author Comment

by:thirdrockit
ID: 40195250
This code is working but the pictures disappear unless i click the image box and it only shows one at a time.  So if i click image1, it displays, but then 2 and 3 does not.  I click 2, and only 2 shows, 1 and 3 disappears.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Me.Range("C2:C50")) Is Nothing Then
    Sheet6.Image1.Picture = LoadPicture(Sheet5.Range("C2").Value)
    Sheet6.Image2.Picture = LoadPicture(Sheet5.Range("C3").Value)
    Sheet6.Image3.Picture = LoadPicture(Sheet5.Range("C4").Value)
End If

End Sub
0
 

Author Comment

by:thirdrockit
ID: 40196939
Is there any way to create a run button to populate the pictures since the code i have is not refreshing and only showing 1 at a time?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thirdrockit
ID: 40197973
I have tried to create a command button but my limited knowledge in vba and the lack of examples on the web as caused me to hit a road block.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40222456
How did you manually insert the pictures?  I need a better understanding of what your image controls are.

I thought you might have just been inserting pictures from files, but It looks like you might have predefined destinations (controls).
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40225283
@thirdrockit

Do you have a working solution?  Your more recent comments appear to indicate that the accepted solution comment did not give you what you needed.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40243834
@thirdrockit

I await your responses to my questions
0
 

Author Closing Comment

by:thirdrockit
ID: 40379025
this code works.  Actually have a problem else where in the script but found a work around
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question