Pictures in drop down over a range.

**skyping** · 10-04-2011, 08:28 AM

Hi....once again I'm stuck and need some of your expert help!!!

I have several pictures on one worksheet labelled pict_1 to pict_x, and would like these pictures to be the source of a drop down in a range of cells in another sheet .. e.g. a1-a10.
Once a picture is selected then the picture is displayed in that particular cell, and each cell in the range can be any of the pictures or blank.

I have searched high and low but can't get exactly what I'm after, so hope you can help!

Thanks for looking.

**skyping** · 10-07-2011, 03:55 PM

Anyone have any ideas?

**Mordred** · 10-07-2011, 03:58 PM

Perhaps uploading a workbook that outlines the before and after of what it is you are trying to do would be beneficial to you and us.

**skyping** · 10-07-2011, 05:53 PM

Please find attached sample...really hope you can help!

**skyping** · 10-10-2011, 04:20 AM

Really appreciate some help chaps!

**snb** · 10-10-2011, 05:18 AM

Are you asking for the formatting of the validationlist (showing pictures instead of picturename) or are asking for code to put the chosen picture in a certain cell ?

**skyping** · 10-10-2011, 07:32 AM

Thank you for your reply.

Ideally, I am looking for a way for any of the cells colored yellow to show any of the pictures by means of a combo box / dropdown box with variables pict_1 to pict_x. These are the names of the pictures on sheet 2. Once a selection has been made, that particular cell changes to the particular picture. Each picture can be shown for each or any cell. Hope this is clear and once again many thanks!

**royUK** · 10-10-2011, 08:42 AM

I have a technique that does this but I can't access it until later

**royUK** · 10-11-2011, 07:48 AM

You cannot add anything to the actual cell that has the data validation. What is the purpose of this?

**skyping** · 10-11-2011, 09:23 AM

Sorry if there is any confusion here. I will try to make it as straightforward as I can!!!!

I simply need a range of cells to show text in the form of a drop down / combo box, that relates to some pictures....e.g. Picture_Car,Picture_House,Picture_tree etc.

For each cell in a range (I have coloured them yellow in my previous attachment), I wish the user to 'pick' the picture from that selection, and then for that cell (or if this is impossible the cell to the direct right) to change to the relevant picture of that name. Note: These pictures are positioned in the another sheet (say Sheet 2).

Each cell in the range can have any picture once or indeed zero times or any number of times.

I really hope that this is clear and that you can help.

Once again many thanks for looking!

**royUK** · 10-11-2011, 09:36 AM

The images are not the same size as the cells so would need re-sizing.

As I said earlier I can use the selection to display the selected image, but the range it occupies needs to be at least the same size as the image

**skyping** · 10-11-2011, 09:58 AM

Hi Roy,

The actual pictures that I will use (for an existing company product line(NOT the ones in my demo file)) are quite small (smaller than the cell), and I an easily make them the same size.

I simply can't see a way of displaying these images over a range of cells....so please suggest suitable methods.
Thanks again!

**snb** · 10-11-2011, 10:55 AM

cfr. the attachment

PS I do not understand what you mean by 'over a range'.

**ndjustin20** · 10-11-2011, 11:14 AM

Hey SNB I tried your workbook in excel 2010 and an error was thrown runtime error 449 Argument not optional...

Looks like the problem code line is highlighted below

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target <> "" Then
c01 = ThisWorkbook.Path & "\" & Target.Value & ".gif"
If Dir(c01) = "" Then
With Sheets("sheet2").Shapes(Target.Value)
.CopyPicture
With .Parent.ChartObjects.Add(, , .Width, .Height).Chart
.Paste
.Export c01, "GIF"
.Parent.Delete
End With
End With
End If
On Error Resume Next
Target.AddComment
Target.Comment.Shape.Fill.UserPicture c01
Target.Comment.Visible = True
End If
End Sub

I also would be interested in doing something like this but could I just send the image to a text box maybe or a slide show? A slide show would be ideal...say I clicked a button and images from another sheet were implemented in a slide show.

**snb** · 10-11-2011, 11:38 AM

Just change into:

Please Login or Register  to view this content.

I uploaded an amended attachment in the previous post.

**skyping** · 10-11-2011, 11:40 AM

Thanks for the reply, I can't get the file to work, and so have attached another demo, which I think explains my problem further.

P.S The range i am refered to simply means that I would like the process of picture selection to work over more than one cell.

Once again thanks for your patience,

**snb** · 10-11-2011, 11:47 AM

I replaced the attachment.

**royUK** · 10-11-2011, 12:00 PM

This doesn't use VBA, but needs the images re-sizing. I've used the smaller horse picture from the last example. Select Picture 4 from the list in A1

This is all done with named ranges & pictures

Pictures in drop down over a range.

LinkBack

Thread Tools

Rate This Thread

Display

Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Re: Pictures in drop down over a range.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions