Hiding / unhiding pictures based on cell value

**supern0va** · 08-14-2012, 10:52 AM

Hi,

The last few days I've been working on a multiple choice quiz in excel.
I have a working version that picks questions from a column and answers from another, and presents it to the user. See attached.

Now I want the questions for chapter 1 to be pictures instead of text. I added some dummy pics in the doc I attached.
I used this tutorial.
It is supposed to hide all pictures and then display one on a specified location [U]if[U] that picture's name corresponds to a cell value (which is a vlookup formula).

But I get an error on the following line

 For Each oPic In Me.Pictures

Run-time error '13': Type mismatch

Complete code for that sub:
(Me.pictures.visible should be false. Is still true in attached doc.)

    Private Sub Worksheet_Calculate()

        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Worksheets("Quiz").Range("L2")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Right
                    Exit For
                End If
            Next oPic
        End With
    End Sub

Does anyone see what I'm doing wrong?

**patel45** · 08-14-2012, 11:43 AM

I can't find object pictures, you can try something like

    Private Sub Worksheet_Calculate()

        Dim oPic As Shape
        With Worksheets("Quiz").Range("L2")
            For Each oPic In Me.Shapes
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left 'Right
                    Exit For
                End If
            Next oPic
        End With
    End Sub

**supern0va** · 08-14-2012, 11:56 AM

Hi Patel,

Thanks. Yes, .Left instead of .Right solved part of the problem.

I also tracked down the second part of the problem. There's a type mismatch because not only the jpegs are detected as pictures, but also the start and reset button

See for yourself.

 Sub Countpics()
MsgBox Worksheets("Quiz").Pictures.Count
End Sub

gives 26 hits: 2 buttons and 24 pictures.

when I remove those buttons, the code above works fine but the sheet itself is useless. :s

The reason you didn't see pictures is probably because they were hidden by