Excel picture question.

[BlackCat2011]

I did some searching but I couldn't find\figure out what to do. I'm terrible with with Excel so I don't know if this is possible.

I'm trying to make a simple-ish spreadsheet to display 1 of 3 pictures depending on a numerical value.

Example:

B2 has a value of 0, B3 will show the picture of a plane.
B2 has a value of 1 to 4, B3 will show a picture of a man falling.
B2 has a value of 5 or more, B3 will show a skull.

As stated earlier, I don't know even know if it's possible. If anyone has any ideas can you break them down for an Excel noob lol

Thanks in advance.

 

[Datsmart]

The following link will show you how to swap between several pictures you insert on your worksheet. It hides all except the one that matches the one listed in the cell of your choice. The site includes a downloadable workbook.
http://www.mcgimpsey.com/excel/lookuppics.html

 

[Steve=True]

Hi Datsmart,

Welcome to the Forums!

You can also do this with the Camera Tool in conjuction with the Choose Function.

Here is a Camera Tool tutorial how-to step-by-step for Excel:
http://www.exceldashboardtemplates.com/?p=778

You can set a camera tool = to a named range and then have the Choose function choose the picture value without VBA.

Hope this helps!

Steve=True

 

[Steve=True]

Sorry duplicate posting error

Steve=True

 

[BlackCat2011]

I have been trying Datsmarts method but ran into an issue. If I post my file, could someone look at it? I thought I had it figured out but as I stated in my first post, I suck at Excel, lol.

Just in case: http://dl.dropbox.com/u/14168801/Survival.xlsx

 

[BlackCat2011]

Well, good news. Progress has been made. Now a couple of more issue/questions popped up.

I got it to finally change images depending on which number I enter. The problem is it only does it in one cell (C3). I'm assuming I need to mod something in the macro portion I just don't know what to put to make it apply to all the cells needed (C3-C9, G3-G9, K3-K9, O3-O9).

Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
       With Range("C3")
            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

I tried changing C3 to C3:C9 but it wasn't working. Any ideas?
Also, what can I change to make the images centered in the cell? As usual, what I thought might work didn't. Story of my life :D

Just in case, here is a copy:
http://dl.dropbox.com/u/14168801/BlackCat2011.xls

 

[BlackCat2011]

Well I solved another part of my problem with images I didn't want hidden. If I can just figure out how to fix the positioning I will be set. =D

Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        Shapes("Sur").Visible = True
        Shapes("Lava").Visible = True
        Shapes("Planekey").Visible = True
        Shapes("Mankey").Visible = True
        Shapes("Skullkey").Visible = True
       With Range("C3")
            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

Edit: Well, I fixed the range. It's bloated but it works, lol.

 

[Datsmart]

I have been playing with your request and came up with the following Change Event code to insert pictures into your sample sheet.
I used Data Validation Lists on each of your numbered cells in the 4 vertical ranges.
The code works properly if the cells are changed using the dropdown Validation Lists, it does not work when the cells are manually edited.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
'Continue only if Target cell is within proper ranges
With Target
    If .Count <> 1 Then Exit Sub 'Exit if more than one cell selected
    Set isect = Intersect(Range(.Address), Range("B3:B9"))
    If Not isect Is Nothing Then
        GoTo DoIt
    End If
    Set isect = Intersect(Range(.Address), Range("F3:F9"))
    If Not isect Is Nothing Then
        GoTo DoIt
    End If
    Set isect = Intersect(Range(.Address), Range("J3:J9"))
    If Not isect Is Nothing Then
        GoTo DoIt
    End If
    Set isect = Intersect(Range(.Address), Range("N3:N9"))
    If Not isect Is Nothing Then
        GoTo DoIt
    End If
End With
Exit Sub
DoIt:
'Delete any existing Picture from adjacent cell
    For Each PicObj In ActiveSheet.Pictures
        On Error Resume Next
        TheLeft = PicObj.TopLeftCell.Address
        Set isect = Application.Intersect(Range(TheLeft), Selection.Offset(0, 1))
        If Not isect Is Nothing Then PicObj.Delete
    Next PicObj
'Insert new Picture into adjacent cell
    'Assign variable for Current Cell
    Curcell = ActiveCell.Address
    'Assign Picture Variable based on cell value
    If ActiveCell.Value = 0 Then
        Pic2Get = "Planekey"
    ElseIf ActiveCell.Value > 0 And ActiveCell.Value < 5 Then
        Pic2Get = "Mankey"
    ElseIf ActiveCell.Value > 4 Then
        Pic2Get = "Skullkey"
    End If
    'Copy Picture to Cell adjacent to Target
    Addy = ActiveCell.Offset(0, 1).Address
    ActiveSheet.Pictures(Pic2Get).Copy
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
    'Change Picture Name to simplify Deletion if needed
    Selection.Name = Pic2Get & Addy
    'Unselect Picture
    Range(Curcell).Activate
End Sub

The code assumes you have existing Pictures by the names "Planekey", "Mankey", and "Skullkey" on the worksheet to copy from.

 

[BlackCat2011]

I guess I should have reported back, but after losing some sleep I finally finished it up. It's really bloated but it will suit me for what I need. Thanks for linking that site in your original post, it was a big help