Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pictures in Dropdown
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pictures in Dropdown
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pictures in Dropdown
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pictures in Dropdown
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pictures in Dropdown
Bernie,
Thanks. You are a hero. All works perfect now. You made my day! Best regards, Peter "Bernie Deitrick" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to create a dropdown list with pictures/icons ? | Excel Discussion (Misc queries) | |||
Multiple dropdown list with pictures | Excel Discussion (Misc queries) | |||
Storing Clip Art pictures in My Pictures folder | Excel Discussion (Misc queries) | |||
Excel's Compress Pictures or deleting pictures doesn't seem work | Excel Discussion (Misc queries) | |||
offer dropdown options based on another dropdown | Excel Discussion (Misc queries) |