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

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.
thirdrockitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott CraigWebmasterCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thirdrockitAuthor Commented:
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
thirdrockitAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

thirdrockitAuthor Commented:
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
aikimarkCommented:
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
aikimarkCommented:
@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
aikimarkCommented:
@thirdrockit

I await your responses to my questions
0
thirdrockitAuthor Commented:
this code works.  Actually have a problem else where in the script but found a work around
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.