Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate a pictture when a name is selected
I am trying to have a picture from one worksheet populate when a name is
selected from a list. In other words, if I select Jane Doe, I would like her picture to appear in a cell above. I have tried an IF statement as well as a vlookup, I cannot get it to work. Any suggestions? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate a pictture when a name is selected
P.M. Naughton,
You can do that using event code. Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. I've assumed that your pictures are stored on a sheet named "Pictures" and that Jane Doe's picture is named "Jane Doe", and that the cell you want to enter the name into is cell A2, so that the picture appears in cell A1. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myShape As Shape Dim SC As Range Dim mySh As Worksheet If Target.Cells.Count 1 Then Exit Sub If Target.Address < "$A$2" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False Set mySht = ActiveSheet On Error Resume Next For Each myShape In mySht.Shapes If myShape.Name Like "*Final" Then myShape.Delete Next myShape Worksheets("Pictures").Select ActiveSheet.Shapes(Target.Value).Select Selection.Copy mySht.Select Target.Offset(-1, 0).Select ActiveSheet.Paste Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final" Target.Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub "pmnaughton" wrote in message ... I am trying to have a picture from one worksheet populate when a name is selected from a list. In other words, if I select Jane Doe, I would like her picture to appear in a cell above. I have tried an IF statement as well as a vlookup, I cannot get it to work. Any suggestions? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate a pictture when a name is selected
1. put all the pictures on a worksheet
2. assign each picture a name and record the name in some column, say column G 3. move the pictures on top of each other (like cards in a deck) 4. put data validation in a cell, say A1, that allows the user to pick a name from the list on column G 5. in a standard module, put the following macro: Sub revealOne() Dim p As Shape i = 1 For Each p In ActiveSheet.Shapes If p.Name = Range("A1").Value Then p.Visible = True Else p.Visible = False End If Next End Sub 6. in the worksheet code area put the following event macro: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub Call revealOne End Sub When the user picks a name after clicking on A1, the correct picture will appear. -- Gary''s Student - gsnu200826 "pmnaughton" wrote: I am trying to have a picture from one worksheet populate when a name is selected from a list. In other words, if I select Jane Doe, I would like her picture to appear in a cell above. I have tried an IF statement as well as a vlookup, I cannot get it to work. Any suggestions? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populate a pictture when a name is selected
And to control the position of the picture more closely, use code like this... Replace this:
Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final" With this: Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final" Selection.Top = Target.Offset(-1, 0).Top + 10 Selection.Left = Target.Offset(-1, 0).Left + 10 HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... P.M. Naughton, You can do that using event code. Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. I've assumed that your pictures are stored on a sheet named "Pictures" and that Jane Doe's picture is named "Jane Doe", and that the cell you want to enter the name into is cell A2, so that the picture appears in cell A1. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myShape As Shape Dim SC As Range Dim mySh As Worksheet If Target.Cells.Count 1 Then Exit Sub If Target.Address < "$A$2" Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False Set mySht = ActiveSheet On Error Resume Next For Each myShape In mySht.Shapes If myShape.Name Like "*Final" Then myShape.Delete Next myShape Worksheets("Pictures").Select ActiveSheet.Shapes(Target.Value).Select Selection.Copy mySht.Select Target.Offset(-1, 0).Select ActiveSheet.Paste Selection.Name = "'" & mySht.Name & "'!" & Selection.Name & "Final" Target.Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub "pmnaughton" wrote in message ... I am trying to have a picture from one worksheet populate when a name is selected from a list. In other words, if I select Jane Doe, I would like her picture to appear in a cell above. I have tried an IF statement as well as a vlookup, I cannot get it to work. Any suggestions? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto populate selected cells | Excel Discussion (Misc queries) | |||
Cells are selected but aren't displayed as selected | Excel Discussion (Misc queries) | |||
Cells are selected but aren't displayed as selected | Excel Worksheet Functions | |||
A validated List which link to selected cells according to what is selected on the list | Excel Worksheet Functions | |||
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. | Excel Worksheet Functions |