If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Insert Picture from dropdown selection
I'm working at a Marina and trying to make a comparison chart for different
types of boats. I am trying to make a list, and based upon the users selection of a boat, insert a corresponding picture to match the boat selected from that list. I've researched other thread discussions, and tried to approach it this way, as reccommended by Ron Coder Select Sheet2 and turn off Grid Lines (ToolsOptionsView tab:Uncheck Grid Lines) 1)For each picture to be displayed: 1a. InsertPicture from file. (select picture, put it in the sheet and resize it). 1b. Select the range of cells that contains the picture. 1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text: Example for a picture of an Elephant on cells A210: Select those cells InsertNameDefine Name: picElephant 2)Build your data validation list on a cell in Sheet1 and pick one of the items. If you need help: use Debra Dalgleish's site: http://www.contextures.com/xlDataVal01.html 3)Create a dynamic range name that refers to that cell: InsertNameDefine Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) ....or whatever cell you chose. 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1. 5)With picture selected, type this in the formula bar, then press [Enter]: =ShowMyPic After trying this method, instead of getting the picture inserted, I get the name of the cells that the picture is in...(the name I gave the cells that contained the picture). It does change according to my selection from the list, but doesnt display the picture. The text that is returned instead of the picture seems distorted as well. Any ideas of what I could be doing wrong? I've also tried making this work through macros which I am even less familiar with. I was able to get that to work for one selection, but I dont know how to properly code it to work for every boat in my list. I used this code reccommended by Bernie Deitrick: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address "$A$1" Then Exit Sub If Target.Value = "Yes" Then Application.ScreenUpdating = False Range("B9").Select ActiveSheet.Pictures.Insert( _ "C:\Documents and Settings\PHIL\My Documents\My Pictures\test.jpg").Select Selection.Name = "PictureName" Range("A2").Select Application.ScreenUpdating = True Else On Error Resume Next ActiveSheet.Shapes("PictureName").Delete End If End Sub I assume I would need to somehow nest this or create If - Else commands? Also to clarify, I did change the file names and paths to those that matched my project. It worked for one selection, but I don't know how to code it for a list of say 40 boats Any suggestions of the easiest way to go about doing this? Ive been trying for a few days now, and could use any help I can get! |
#2
|
|||
|
|||
Insert Picture from dropdown selection
I'll try to address the method I posted.
With a value in the drop down list, you should be able to: EditGo to In the reference field, type: ShowMyPic Click the [OK] button That should bring you to the range that contains the picture that is referenced. If it does not, then the problem is in your definition of ShowMyPic Example: with this definition... Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel, then ShowMyPic will refer to that range. In order to help, I'll need a little more information: 1)What is the exact location of your dropdown list (sheet name and cell address)? 2)What is the exact name you gave to one of the pictures? *********** Regards, Ron XL2002, WinXP-Pro "Mike at Channel" wrote: I'm working at a Marina and trying to make a comparison chart for different types of boats. I am trying to make a list, and based upon the users selection of a boat, insert a corresponding picture to match the boat selected from that list. I've researched other thread discussions, and tried to approach it this way, as reccommended by Ron Coder Select Sheet2 and turn off Grid Lines (ToolsOptionsView tab:Uncheck Grid Lines) 1)For each picture to be displayed: 1a. InsertPicture from file. (select picture, put it in the sheet and resize it). 1b. Select the range of cells that contains the picture. 1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text: Example for a picture of an Elephant on cells A210: Select those cells InsertNameDefine Name: picElephant 2)Build your data validation list on a cell in Sheet1 and pick one of the items. If you need help: use Debra Dalgleish's site: http://www.contextures.com/xlDataVal01.html 3)Create a dynamic range name that refers to that cell: InsertNameDefine Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) ...or whatever cell you chose. 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1. 5)With picture selected, type this in the formula bar, then press [Enter]: =ShowMyPic After trying this method, instead of getting the picture inserted, I get the name of the cells that the picture is in...(the name I gave the cells that contained the picture). It does change according to my selection from the list, but doesnt display the picture. The text that is returned instead of the picture seems distorted as well. Any ideas of what I could be doing wrong? I've also tried making this work through macros which I am even less familiar with. I was able to get that to work for one selection, but I dont know how to properly code it to work for every boat in my list. I used this code reccommended by Bernie Deitrick: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address "$A$1" Then Exit Sub If Target.Value = "Yes" Then Application.ScreenUpdating = False Range("B9").Select ActiveSheet.Pictures.Insert( _ "C:\Documents and Settings\PHIL\My Documents\My Pictures\test.jpg").Select Selection.Name = "PictureName" Range("A2").Select Application.ScreenUpdating = True Else On Error Resume Next ActiveSheet.Shapes("PictureName").Delete End If End Sub I assume I would need to somehow nest this or create If - Else commands? Also to clarify, I did change the file names and paths to those that matched my project. It worked for one selection, but I don't know how to code it for a list of say 40 boats Any suggestions of the easiest way to go about doing this? Ive been trying for a few days now, and could use any help I can get! |
#3
|
|||
|
|||
Insert Picture from dropdown selection
Ron,
Thanks for the response, Here's exactly what I've created as a trial experiment: 3 worksheets: Sheet one contains a drop down list created using data validation in cell A5. I want the picture that matches the selction from that to be displayed in cell A2 of that same sheet, sheet 1. On Sheet two I have three pictures of boats. Picture one is placed over cells A1-D6 ( =Sheet2!$A$1:$D$6) and is named "picOne". Picture two is placed over cells A7- D12 (=Sheet2!$A$7:$D$12) and is named "picTwo". Picture three is placed over cells A13- D18 (=Sheet2!$A$13:$D$18) and is named "picThree". Finally on my third worksheet I have my list of names (One, Two, and Three) on cells A1-A3 used for my data validation on sheet 1. "Ron Coderre" wrote: I'll try to address the method I posted. With a value in the drop down list, you should be able to: EditGo to In the reference field, type: ShowMyPic Click the [OK] button That should bring you to the range that contains the picture that is referenced. If it does not, then the problem is in your definition of ShowMyPic Example: with this definition... Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel, then ShowMyPic will refer to that range. In order to help, I'll need a little more information: 1)What is the exact location of your dropdown list (sheet name and cell address)? 2)What is the exact name you gave to one of the pictures? *********** Regards, Ron XL2002, WinXP-Pro "Mike at Channel" wrote: I'm working at a Marina and trying to make a comparison chart for different types of boats. I am trying to make a list, and based upon the users selection of a boat, insert a corresponding picture to match the boat selected from that list. I've researched other thread discussions, and tried to approach it this way, as reccommended by Ron Coder Select Sheet2 and turn off Grid Lines (ToolsOptionsView tab:Uncheck Grid Lines) 1)For each picture to be displayed: 1a. InsertPicture from file. (select picture, put it in the sheet and resize it). 1b. Select the range of cells that contains the picture. 1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text: Example for a picture of an Elephant on cells A210: Select those cells InsertNameDefine Name: picElephant 2)Build your data validation list on a cell in Sheet1 and pick one of the items. If you need help: use Debra Dalgleish's site: http://www.contextures.com/xlDataVal01.html 3)Create a dynamic range name that refers to that cell: InsertNameDefine Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) ...or whatever cell you chose. 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1. 5)With picture selected, type this in the formula bar, then press [Enter]: =ShowMyPic After trying this method, instead of getting the picture inserted, I get the name of the cells that the picture is in...(the name I gave the cells that contained the picture). It does change according to my selection from the list, but doesnt display the picture. The text that is returned instead of the picture seems distorted as well. Any ideas of what I could be doing wrong? I've also tried making this work through macros which I am even less familiar with. I was able to get that to work for one selection, but I dont know how to properly code it to work for every boat in my list. I used this code reccommended by Bernie Deitrick: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address "$A$1" Then Exit Sub If Target.Value = "Yes" Then Application.ScreenUpdating = False Range("B9").Select ActiveSheet.Pictures.Insert( _ "C:\Documents and Settings\PHIL\My Documents\My Pictures\test.jpg").Select Selection.Name = "PictureName" Range("A2").Select Application.ScreenUpdating = True Else On Error Resume Next ActiveSheet.Shapes("PictureName").Delete End If End Sub I assume I would need to somehow nest this or create If - Else commands? Also to clarify, I did change the file names and paths to those that matched my project. It worked for one selection, but I don't know how to code it for a list of say 40 boats Any suggestions of the easiest way to go about doing this? Ive been trying for a few days now, and could use any help I can get! |
#4
|
|||
|
|||
Insert Picture from dropdown selection
Have you had a look at JE McGimsey's site: http://www.mcgimpsey.com/excel/lookuppics.html I think it is exactly what you're trying to do. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=513797 |
#5
|
|||
|
|||
Insert Picture from dropdown selection
You don't mention if the model you created works. Does it?
If not...did you create the dynamic ShowMyPic range? *********** Regards, Ron XL2002, WinXP-Pro "Mike at Channel" wrote: Ron, Thanks for the response, Here's exactly what I've created as a trial experiment: 3 worksheets: Sheet one contains a drop down list created using data validation in cell A5. I want the picture that matches the selction from that to be displayed in cell A2 of that same sheet, sheet 1. On Sheet two I have three pictures of boats. Picture one is placed over cells A1-D6 ( =Sheet2!$A$1:$D$6) and is named "picOne". Picture two is placed over cells A7- D12 (=Sheet2!$A$7:$D$12) and is named "picTwo". Picture three is placed over cells A13- D18 (=Sheet2!$A$13:$D$18) and is named "picThree". Finally on my third worksheet I have my list of names (One, Two, and Three) on cells A1-A3 used for my data validation on sheet 1. "Ron Coderre" wrote: I'll try to address the method I posted. With a value in the drop down list, you should be able to: EditGo to In the reference field, type: ShowMyPic Click the [OK] button That should bring you to the range that contains the picture that is referenced. If it does not, then the problem is in your definition of ShowMyPic Example: with this definition... Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) If cell A1 on Sheet1 contains "Camel" and you have a range named: picCamel, then ShowMyPic will refer to that range. In order to help, I'll need a little more information: 1)What is the exact location of your dropdown list (sheet name and cell address)? 2)What is the exact name you gave to one of the pictures? *********** Regards, Ron XL2002, WinXP-Pro "Mike at Channel" wrote: I'm working at a Marina and trying to make a comparison chart for different types of boats. I am trying to make a list, and based upon the users selection of a boat, insert a corresponding picture to match the boat selected from that list. I've researched other thread discussions, and tried to approach it this way, as reccommended by Ron Coder Select Sheet2 and turn off Grid Lines (ToolsOptionsView tab:Uncheck Grid Lines) 1)For each picture to be displayed: 1a. InsertPicture from file. (select picture, put it in the sheet and resize it). 1b. Select the range of cells that contains the picture. 1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text: Example for a picture of an Elephant on cells A210: Select those cells InsertNameDefine Name: picElephant 2)Build your data validation list on a cell in Sheet1 and pick one of the items. If you need help: use Debra Dalgleish's site: http://www.contextures.com/xlDataVal01.html 3)Create a dynamic range name that refers to that cell: InsertNameDefine Name: ShowMyPic RefersTo: =INDIRECT("pic"&Sheet1!$A$1) ...or whatever cell you chose. 4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1. 5)With picture selected, type this in the formula bar, then press [Enter]: =ShowMyPic After trying this method, instead of getting the picture inserted, I get the name of the cells that the picture is in...(the name I gave the cells that contained the picture). It does change according to my selection from the list, but doesnt display the picture. The text that is returned instead of the picture seems distorted as well. Any ideas of what I could be doing wrong? I've also tried making this work through macros which I am even less familiar with. I was able to get that to work for one selection, but I dont know how to properly code it to work for every boat in my list. I used this code reccommended by Bernie Deitrick: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address "$A$1" Then Exit Sub If Target.Value = "Yes" Then Application.ScreenUpdating = False Range("B9").Select ActiveSheet.Pictures.Insert( _ "C:\Documents and Settings\PHIL\My Documents\My Pictures\test.jpg").Select Selection.Name = "PictureName" Range("A2").Select Application.ScreenUpdating = True Else On Error Resume Next ActiveSheet.Shapes("PictureName").Delete End If End Sub I assume I would need to somehow nest this or create If - Else commands? Also to clarify, I did change the file names and paths to those that matched my project. It worked for one selection, but I don't know how to code it for a list of say 40 boats Any suggestions of the easiest way to go about doing this? Ive been trying for a few days now, and could use any help I can get! |
#6
|
|||
|
|||
Insert Picture from dropdown selection
Cutter,
THANK YOU! I've been trying different methods for over a week now, and finally got it to work. Ron, thanks as well for the response, I must have been making some small error that I didn't recognize, a wrong reference or something, but since the V lookup method worked for me, I will just go with that. That is exactly how I wanted it to work. I appreciate all your help, and prompt reply, and I think the message board is a great resource. Thanks for helping out! "Cutter" wrote: Have you had a look at JE McGimsey's site: http://www.mcgimpsey.com/excel/lookuppics.html I think it is exactly what you're trying to do. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=513797 |
#7
|
|||
|
|||
Insert Picture from dropdown selection
One More problem,
that solution worked great for one pull down list, but I'm trying to compare two boats by selection, so I need apply this code to two dropdown lists: Option Explicit Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("B12") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub This code works for my output in cell B12 from the dropdown list in B11. Now I would like to do the same for my second dropdown list in C11, and would like to display it in C12. Both dropdown lists are coming from the same list named picTable on Sheet 2 as informed by your excel help. I can't seem to just duplicate the code and change the range on the second code. I'm guessing I have to nest the two codes into one code for that sheet? I don't know how to properly code this. Any help? Thanks again, Mike "Cutter" wrote: Have you had a look at JE McGimsey's site: http://www.mcgimpsey.com/excel/lookuppics.html I think it is exactly what you're trying to do. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=513797 |
#8
|
|||
|
|||
Insert Picture from dropdown selection
Try this: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("B12") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With With Range("C12") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub Make sure you change the formula in C12 to refer to the drop down list in C11 And you'll have to do something to your lists so that the name for the picture already showing (from one list selection) does not show up in the other. This is because each picture can only be shown once. I think you'll have to have 2 lists. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=513797 |
#9
|
|||
|
|||
Insert Picture from dropdown selection
Wonderful. Thank you Cutter, your codes work perfectly. This is exactly how I
wanted it to work. I did make two seperate lists and associated two different pictures, and now I can select two of the same boats without losing the picture(even though the point is to compare two different boats). I definitely need some coding practice, but having someone to point you in the right direction helps. I would have been lost without the help. Thanks. Thanks to Ron as well for your approach. My minimal experience with Macros made the approach a bit more complicated. It kept returning the text name of the picture instead of the picture itself. I'm sure it had to do with one of my definitions, or a small error with my code. But thanks for your input as well. Mike "Cutter" wrote: Try this: Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("B12") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With With Range("C12") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub Make sure you change the formula in C12 to refer to the drop down list in C11 And you'll have to do something to your lists so that the name for the picture already showing (from one list selection) does not show up in the other. This is because each picture can only be shown once. I think you'll have to have 2 lists. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=513797 |
#10
|
|||
|
|||
Insert Picture from dropdown selection
You're very welcome. I'm a VBA novice myself so the real thanks for what I was able to give you goes to JE McGimpsey for writing the original code and providing it to people like you and me in a way that we can understand it and get it working for our specific needs. PS - Do I get a great deal on a boat? ;-) PPS - It would have to be an icebreaker if I do. Canada, eh. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=513797 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Setting up protected document that can insert picture (photo ID template) | Craig | Page Layout | 5 | July 13th, 2007 01:15 PM |
insert picture to a specfic range of cells | CORY | General Discussion | 8 | February 3rd, 2006 05:50 PM |
Excel 2003 - insert picture - Not Responding | trinity | Setting up and Configuration | 4 | October 17th, 2005 12:46 PM |
Making a frame to insert a picture | Jean | Powerpoint | 2 | September 11th, 2005 11:47 PM |
Insert picture with VBA | wschiro | Powerpoint | 2 | April 25th, 2005 06:14 AM |