Having issues with ShowPic and If Statements

[bradjedis]

Greetings,

Here is the situation. I am using the following if statement:

=IF(CD1="Plant", ShowPicD("k:\Downloads\Files\4-13-2016\A1.jpg"), IF(CD1="6200", ShowPicD("k:\Downloads\Files\4-13-2016\A2.jpg"), IF(CD1="6300", ShowPicD("k:\Downloads\Files\4-13-2016\Not Pictured.jpg"),"Not Pictured")))

First, when selecting "Plant" in CD1, it will bring in the picture. However, when picking "6200" it is not getting the second picture (same if I pick the 6300). Also, I am using the following function code, obtained from "http://www.mrexcel.com/forum/excel-ques ... rmula.html"

The code is supposed to delete the picture (if one is there) so we do not get stacked images.

Code: Select all

Function ShowPicD(PicFile As String) As Boolean
'Same as ShowPic except deletes previous picture when picfile changes
Dim AC As Range
Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller

    If PicExists(P) Then
        P.Delete
    Else
        'look for a picture already over cell

        For Each P In ActiveSheet.Shapes
            If P.Type = msoLinkedPicture Then
                If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
                    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
                        P.Delete
                        Exit For
                    End If
                End If
            End If
        Next P
    End If

    Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)

    ShowPicD = True
    Exit Function
Done:
    ShowPicD = False

End Function

Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function

Thanks for any help!

Brad

[Rudi]

I suspect that cell CD1 on your worksheet is formatted as General, meaning that alpha characters are processed as text and numerical characters are processed as numbers. On the other hand, your formula that collects the images is only processing text entries from cell CD1 as the values you pass to the formula are wrapped in double quotes.

The quickest (and probably the best) way to fix your issue is to select cell CD1 and format it to "Text". To do this:

1. Select cell CD1
2. Press CTRL+1 (the #1 above the alpha characters on your keyboard) to activate the Format Cells dialog
3. Select Text from the left hand window in the dialog
4. Choose OK

Now you can type Plant, 6200 and 6300 and the images should appear correctly.

[HansV]

Alternatively, use 6200 and 6300 without quotes:

IF(CD1=6200,...

[bradjedis]

All good now,Thanks!

( tried Hans' first, sorry Rudi)....But for grins, I will try yours as well

[Rudi]

First, last, somewhere in between... It makes no difference as long as you get a solution and its simple and effective.
Glad it's resolved.