PIC

ok, i kinda got it....i'm putting my picture in a cell and a white box over it and then running this code....it works well.

the problem is a bit more complicated now....

i have 15 cells....A1-A15
based on the numbers in those cells (ie. numbers 1-9), i need a certain picture to appear in B1-B15

is there a way to associate a picture to a number? (ie. 3 means this picture) and then modify the macro?

Take a look at this post, similar request:
Code included.
http://www.mrexcel.com/board2/viewtopic.php?p=1228524#1228524

thank you but what i'm doing is a bit more complicated....at least i think...(then again, i may be very wrong).

here is the code that i wrote...for only ONE of the cells (A1).
Is there anyone could make this VERY bad code into an "ok" code?

This will do it a different way all the pictures are in a Folder and a trigger cell has a Dropdown [or other way of indicating a picture name] here the user selects a Picture Name [the Folder has the Pictures named the same as the DropDown Names] and when the user selects a new name [in my sample E10] the picture changes automatically!

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet Module code, like: Sheet1.
Dim myPicSel$, myPicFile$

'Change this info!
'Get selected picture file name [not extenstion] from this cell only.
If Target.Address <> "$E$10" Then Exit Sub
myPicSel = Range("E10").Value
On Error GoTo myErr1

'Load active pic name.
myPicFile = Range("F10").Value

'Remove current picture from sheet.
ActiveSheet.Shapes(myPicFile).Select
Selection.Cut

myErr1:
myPicFile = myPicSel
On Error GoTo myEnd

'Change this info!
'Load selected picture file to sheet.
'Below this cell is where you want the picture to be added!
ActiveSheet.Range("A10").Select

'Change this info!
'Note: Folder path!
'Note: Picture type Extention!
ActiveSheet.Pictures.Insert("U:\Excel\Test\" & myPicFile & ".gif").Select
Selection.Name = myPicFile
Range("F10").Value = myPicFile

'Change picture size here.
Selection.ShapeRange.ScaleWidth 0.35, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.35, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Picture").Visible = False

ActiveSheet.Shapes(myPicFile).Select
Selection.ShapeRange.IncrementLeft 2#
Selection.ShapeRange.IncrementTop 16#
Range("A1").Select

myEnd:
End Sub

ok....first off, wow....
secondly, is there anyway that i could have my pictures in a different worksheets within the same file?

thirdly, if the answer is no to the above question, could you please explain what i need to change in your code? i'm not a 100 percent sure.

thank you so much for your time.

i even tried modifying it but it won't even run....

Yes to all your questions, but the code posted will only work if your pictures are files in a folder. You only need to change the Folder - Path then the trigger cell location and then play with the picture size and display location in the code.


Here is a way to do the same only with hard-coded pictures, where the displayed names are different than the file names:


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet Module code, like: Sheet1.
Dim myFlgSel$, myFlgFile$

'Get selected flags name only.
If Target.Address <> "$E$10" Then Exit Sub
myFlgSel = Range("E10").Value

'Test for no name found!
On Error GoTo myErr1

'Load active flags name.
myFlgFile = Range("F10").Value
'Remove current flag from sheet.
ActiveSheet.Shapes(myFlgFile).Select
Selection.Cut

myErr1:
'Use selected name to load file name.
Select Case myFlgSel
Case "United States of America"
myFlgFile = "us-s"

Case "China"
myFlgFile = "ch"

Case "Poland"
myFlgFile = "poland"

Case "United Kingdom"
myFlgFile = "uk"

Case "England"
myFlgFile = "england"

Case Else
Exit Sub
End Select

'Load selected flag file to sheet.
ActiveSheet.Pictures.Insert("U:\Excel\Test\" & myFlgFile & ".gif").Select
Selection.Name = myFlgFile
Range("F10").Value = myFlgFile

Selection.ShapeRange.ScaleWidth 0.35, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.35, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Picture").Visible = False

ActiveSheet.Shapes(myFlgFile).Select
Selection.ShapeRange.IncrementLeft 2#
Selection.ShapeRange.IncrementTop 16#
Range("A1").Select
End Sub


And, this is a way to make a picture fit the dimentions of a cell:


Sub AddPictureToCell()
'Standard module code, like: Module1.
Dim cell As Object
Dim ws As Worksheet
Dim myRange As Range
Dim myPhotos As ShapeRange

Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1")
Set myRng = ws.Range("B2:B12")

On Error GoTo noPics
Set myPhotos = ws.Pictures.ShapeRange

If myPhotos.Count > 0 Then myPhotos.Delete

noPics:
For Each cell In myRng
cell.ColumnWidth = 7
cell.RowHeight = 30

If cell.Offset(0, 1).Value > 7 Then ws.Pictures.Insert("U:\Excel\Test\aPhoto2.jpg").Select
If cell.Offset(0, 1).Value < 7 Then ws.Pictures.Insert("U:\Excel\Test\aPhoto.jpg").Select

With Selection
.Top = cell.Top
.Left = cell.Left
.Width = cell.Width
.Height = cell.Height
.Placement = xlMoveAndSize
.PrintObject = True
End With

Next cell
Application.ScreenUpdating = True

ActiveSheet.Range("B1").Select
End Sub

joe,
your code is brilliant...it's me who is, for lack of a better word, stupid when it comes to VBA...and i'm trying to learn and follow your code as best as i can but i really don't know what it's doing....

here are some questions....

what exactly is this:

Case "United States of America"
myFlgFile = "us-s"

the images are already in sheet2 of my file.....i need to associate a value to each one...say picture 1 is associated with the number 1 and so on.

everytime the user puts a 4 in sheet1's A1, "picture4" should appear in B1 and so on.

I'm trying to follow the code but i'm not sure if i understand properly. I know your code is really smart...i'm just frustrated a bit at myself for not getting it.

The Dropdown has long descriptive photo names, but the files have shorter names. This code:

Case "United States of America"
myFlgFile = "us-s"

Converts the selected long name to the actual file name so it can be retrived from its Folder and be displayed.

The whole code will need to be re-written to use pictures on a sheet. A photo folder is better on file size, but pictures stored on a sheet are more transportable as they are contained in the one Workbook file.

I do not have any workbook style code handy and it is 7:00 PM here and This is my last post. So, I cannot write some just now.