Click on a link and a picture loads in cell depending on which link you have clicked

Hello

If I have five links in cells A1:A5 with peoples names. How can I
get...

e.g.

If I click on Bill in A2 then a picture of Bill loads in cell D1 and
then click Mark and a picture of Mark loads in D1.

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

I'm not sure what you mean by link in A1 through A5, but I'd try it this way:

Put all 5 pictures on the worksheet and position them where you want them.

Name them to match the values in A1:A5.
Select the picture for Bill and then type Bill in the name box (to the left of
the formulabar) and hit enter.

After all 5 pictures are named nicely and positioned correctly, right click on
the worksheet tab that needs this behavior. Select view code and past this in.
Then back to excel and test it out.

It actually doesn't depend on a click--just select the cell (a1:a5) that you
want to show.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myPictNames As Variant
Dim iCtr As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub

myPictNames = Array("bill", "jim", "mark", "mary", "peo")

On Error Resume Next
For iCtr = LBound(myPictNames) To UBound(myPictNames)
Me.Pictures(myPictNames(iCtr)).Visible = False
Next iCtr
Me.Pictures(Target.Value).Visible = True
On Error GoTo 0

End Sub

I've pretty much thrown any error checking away with that "on error resume next"
statement. So be careful when you set it up.

"gatesheadthunde <" wrote:
>
> Hello
>
> If I have five links in cells A1:A5 with peoples names. How can I
> get...
>
> e.g.
>
> If I click on Bill in A2 then a picture of Bill loads in cell D1 and
> then click Mark and a picture of Mark loads in D1.
>
> Thanks in advance
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com

Thanks for that but I am still unable to get this to work...

'A link to my project'
(http://www.geocities.com/gateshead_t...ictureTest.xls)

The above link is the worksheet I want to use it for. I have a drop
down list now showing some Premiership clubs. With that I have used
VLOOKUP to display the selected teams stadium name.

However, what I want is for a picture of the stadium to appear where
the box.

Thanks


---
Message posted from http://www.ExcelForum.com/

I don't open attachments or links to files.

If your dropdown list is created via data|validation and you're using xl97, then
the worksheet event won't fire.

You could put a button near the dropdown to show/hide the pictures.

"gatesheadthunde <" wrote:
>
> Thanks for that but I am still unable to get this to work...
>
> 'A link to my project'
> (http://www.geocities.com/gateshead_t...ictureTest.xls)
>
> The above link is the worksheet I want to use it for. I have a drop
> down list now showing some Premiership clubs. With that I have used
> VLOOKUP to display the selected teams stadium name.
>
> However, what I want is for a picture of the stadium to appear where
> the box.
>
> Thanks
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com

Dave,

I have managed to get this to work now, thank you.

On problem however... after selecting the name from the drop down list
the cell with Stadium name automatically changes and the picture only
shows when that cell is selected. Is there anyway to get it too show
instantly?

Thanks


---
Message posted from http://www.ExcelForum.com/

What version of excel are you using?

With xl2k or higher, you can use a worksheet change event--I'd toss the version
based on selection:

It sounds like you changed from A1:A5 to just one cell, though. I used A1 in
this code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPictNames As Variant
Dim iCtr As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

myPictNames = Array("bill", "jim", "mark", "mary", "peo")

On Error Resume Next
For iCtr = LBound(myPictNames) To UBound(myPictNames)
Me.Pictures(myPictNames(iCtr)).Visible = False
Next iCtr
Me.Pictures(Target.Value).Visible = True
On Error GoTo 0

End Sub


But if you're using xl97, this note from Debra Dalgleish's site may apply
http://www.contextures.com/xlDataVal08.html:

In Excel 97, selecting an item from a Data Validation dropdown list
does not trigger a Change event, unless the list items have been typed in
the Data Validation dialog box. In this version, you can add a button to
the worksheet, and run the code by clicking the button. To see an
example, go to the Sample Worksheets page, and under the Filters
heading, find Product List by Category, and download the
ProductsList97.xls file.



"gatesheadthunde <" wrote:
>
> Dave,
>
> I have managed to get this to work now, thank you.
>
> On problem however... after selecting the name from the drop down list
> the cell with Stadium name automatically changes and the picture only
> shows when that cell is selected. Is there anyway to get it too show
> instantly?
>
> Thanks
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com

That formula worked perfect.

Thank you very much.




---
Message posted from http://www.ExcelForum.com/