Pictures in Dropdown

[Peter]

Hi

I found an excellent example file for creating dropdowns in Excel to show
pictures. Its on www.contextures.on.ca/excelfiles (DV0049 - ClipArt
Selection).

However, I would like to have the "picture repository" on another worksheet
and not on the same as the drop down.

Can sombody support? I guess I need to change something in the macro but I
am not familiar with that at all.

Thanks for your support!

Best regards,
Peter

 

[Bernie Deitrick]

Peter,

That was my code, so I guess I'm qualified to change it ;-)

In the sheet's codemodule, change

ActiveSheet.Shapes(myCell.Value).Select
Selection.Copy

to

Worksheets("Sheet With Pictures").Shapes(myCell.Value).Copy

where, obviously, you need to change "Sheet With Pictures" to the actual name of the sheet with the
pictures. In the code below, that sheet is "Sheet1".....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()

Dim myCell As Range
Dim mySel As Range

Set mySel = Selection

With Application
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False

On Error Resume Next
For Each myCell In Range("KeyCells")
ActiveSheet.Shapes(myCell.Address & "Final").Delete
Worksheets("Sheet1").Shapes(myCell.Value).Copy
myCell.Offset(0, 1).Select
ActiveSheet.Paste
Selection.Name = myCell.Address & "Final"
Selection.ShapeRange.ZOrder msoSendToBack
Next myCell

mySel.Select

.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

 

[Peter]

Bernie,

Great! It works. Thanks a lot! Guess you are really qualified

However, I have one little problem with the pictures being pasted on the
worksheet. The various pictures (four different) don't appear on the same
position. They are slightly shifted.

Any idea why this happens? The pics (icons) have all the same size.

Thanks in advance,
Peter

 

[Bernie Deitrick]

Peter,

The copied picture will reflect the alignment relative to the cells where the originals are stored -
in other words, line up the originals the way that you want the copies to appear.

HTH,
Bernie
MS Excel MVP

 

[Peter]

Bernie,

Thanks. You are a hero. All works perfect now. You made my day!

Best regards,
Peter