Tags Tags

Runtime Error 13 and a really ugly code...

July 20th, 2010 - 12:39 pm ET by TheMilkGuy | Report spam
Help Create a new topic
Hi folks,

Based on the show/hide pictures information I read here:
http://www.mcgimpsey.com/excel/lookuppics.html

I edited it to work for my scenario. The first part is included
because I'm not sure if it is contributing to the problem, as my VB
knowledge is less than nil.

I get the Runtime Error 13: Type Mismatch error. Could someone
please tell me how I can fix it?

At the same time, if there is a way to 'clean up' the code, I would
appreciate that too.

Many thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula = False Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If

End Sub

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True
With Range("bo1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO2")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO4")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO6")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO7")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO8")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO9")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO10")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO11")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO12")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO13")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO14")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO15")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO16")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO17")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO18")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO19")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With

End Sub
email Follow the discussionReplies 12 repliesReplies Make a reply

Similar topics

Display all similar topics

Replies

#1 TheMilkGuy
July 20th, 2010 - 09:38 pm ET | Report spam
Matthew,

Your code is just like the website's code, which works fine, except
that instead of one cell referencing one photo to display, I need the
code to look at each cell from BO1 to BO19 and if the Picture # is
listed in any of those cells, the image will display. I am using
transparent gif files, so they overlay without issue.

For instance, if BO1 reads Picture 3, Picture 3 would display
If BO2 is empty, no image appears
If BO3 reads Picture 12, Picture 12 would display atop Picture 3
... et cetera

I was hoping that getting this code working by simplifying it would
get rid of the Type Mismatch error, would you agree?

Thanks!
Craig

On Jul 20, 10:28 pm, Matthew wrote:
On 20 July, 17:39, TheMilkGuy wrote:



> Hi folks,

> Based on the show/hide pictures information I read here:http://www.mcgimpsey.com/excel/lookuppics.html

> I edited it to work for my scenario.  The first part is included
> because I'm not sure if it is contributing to the problem, as my VB
> knowledge is less than nil.

> I get the Runtime Error 13:  Type Mismatch error.  Could someone
> please tell me how I can fix it?

> At the same time, if there is a way to 'clean up' the code, I would
> appreciate that too.

> Many thanks!

> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.HasFormula = False Then
> Application.EnableEvents = False
> Target.Value = UCase(Target.Value)
> Application.EnableEvents = True
> End If

> End Sub

> Private Sub Worksheet_Calculate()
>         Dim oPic As Picture
>         Me.Pictures.Visible = False
>         Me.Pictures("Picture 1").Visible = True
>         With Range("bo1")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO2")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO3")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO4")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO5")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO6")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO7")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO8")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO9")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO10")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO11")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO12")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO13")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO14")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO15")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO16")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO17")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO18")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With
>         With Range("BO19")
>             For Each oPic In Me.Pictures
>                 If oPic.Name = .Text Then
>                     oPic.Visible = True
>                     oPic.Top = AK1
>                     oPic.Left = .Left
>                     Exit For
>                 End If
>             Next oPic
>         End With

>     End Sub

I think i know what you are doing

I used this to put 1 picture up at a time from cell F1
It works on a prety simple level and I used it to display various
pictures the content of F1 was controled else where to make it work.

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

Hope it helps

Matthew
Replies Reply to this message
#2 Per Jessen
July 20th, 2010 - 10:10 pm ET | Report spam
Hi

It is not easy to figure out why you get the 'Type Mismatch error',
without knowing which line is causing the error (Click debug and see
which line is highlighted!).
In the line: oPic.Top = AK1, what is AK1 supposed to be? AK1 is seen
as a variable which has got no value, and it can cause the error. Try
to use the line below and see if it solve the problem:

oPic.Top = .Top


With your additional explanation, I think your code can be reduced to
the below.:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim rngPicture As Range

Set rngPicture = Range("BO1:BO19")
Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True
For Each cell In rngPicture.Cells
With cell
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
Next
End Sub

Regards,
Per

On 21 Jul., 03:38, TheMilkGuy wrote:
Matthew,

Your code is just like the website's code, which works fine, except
that instead of one cell referencing one photo to display, I need the
code to look at each cell from BO1 to BO19 and if the Picture # is
listed in any of those cells, the image will display.  I am using
transparent gif files, so they overlay without issue.

For instance, if BO1 reads Picture 3, Picture 3 would display
If BO2 is empty, no image appears
If BO3 reads Picture 12, Picture 12 would display atop Picture 3
... et cetera

I was hoping that getting this code working by simplifying it would
get rid of the Type Mismatch error, would you agree?

Thanks!
Craig

On Jul 20, 10:28 pm, Matthew wrote:



> On 20 July, 17:39, TheMilkGuy wrote:

> > Hi folks,

> > Based on the show/hide pictures information I read here:http://www.mcgimpsey.com/excel/lookuppics.html

> > I edited it to work for my scenario.  The first part is included
> > because I'm not sure if it is contributing to the problem, as my VB
> > knowledge is less than nil.

> > I get the Runtime Error 13:  Type Mismatch error.  Could someone
> > please tell me how I can fix it?

> > At the same time, if there is a way to 'clean up' the code, I would
> > appreciate that too.

> > Many thanks!

> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.HasFormula = False Then
> > Application.EnableEvents = False
> > Target.Value = UCase(Target.Value)
> > Application.EnableEvents = True
> > End If

> > End Sub

> > Private Sub Worksheet_Calculate()
> >         Dim oPic As Picture
> >         Me.Pictures.Visible = False
> >         Me.Pictures("Picture 1").Visible = True
> >         With Range("bo1")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO2")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO3")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO4")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO5")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO6")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO7")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO8")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO9")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO10")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO11")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO12")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO13")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO14")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO15")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO16")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO17")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO18")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO19")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With

> >     End Sub

> I think i know what you are doing

> I used this to put 1 picture up at a time from cell F1

...

læs mere »- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -
Replies Reply to this message
#3 Matthew
July 21st, 2010 - 05:44 am ET | Report spam
On 21 July, 02:38, TheMilkGuy wrote:
Matthew,

Your code is just like the website's code, which works fine, except
that instead of one cell referencing one photo to display, I need the
code to look at each cell from BO1 to BO19 and if the Picture # is
listed in any of those cells, the image will display.  I am using
transparent gif files, so they overlay without issue.

For instance, if BO1 reads Picture 3, Picture 3 would display
If BO2 is empty, no image appears
If BO3 reads Picture 12, Picture 12 would display atop Picture 3
... et cetera

I was hoping that getting this code working by simplifying it would
get rid of the Type Mismatch error, would you agree?

Thanks!
Craig

On Jul 20, 10:28 pm, Matthew wrote:

> On 20 July, 17:39, TheMilkGuy wrote:

> > Hi folks,

> > Based on the show/hide pictures information I read here:http://www.mcgimpsey.com/excel/lookuppics.html

> > I edited it to work for my scenario.  The first part is included
> > because I'm not sure if it is contributing to the problem, as my VB
> > knowledge is less than nil.

> > I get the Runtime Error 13:  Type Mismatch error.  Could someone
> > please tell me how I can fix it?

> > At the same time, if there is a way to 'clean up' the code, I would
> > appreciate that too.

> > Many thanks!

> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.HasFormula = False Then
> > Application.EnableEvents = False
> > Target.Value = UCase(Target.Value)
> > Application.EnableEvents = True
> > End If

> > End Sub

> > Private Sub Worksheet_Calculate()
> >         Dim oPic As Picture
> >         Me.Pictures.Visible = False
> >         Me.Pictures("Picture 1").Visible = True
> >         With Range("bo1")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO2")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO3")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO4")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO5")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO6")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO7")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO8")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO9")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO10")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO11")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO12")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO13")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO14")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO15")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO16")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO17")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO18")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With
> >         With Range("BO19")
> >             For Each oPic In Me.Pictures
> >                 If oPic.Name = .Text Then
> >                     oPic.Visible = True
> >                     oPic.Top = AK1
> >                     oPic.Left = .Left
> >                     Exit For
> >                 End If
> >             Next oPic
> >         End With

> >     End Sub

> I think i know what you are doing

> I used this to put 1 picture up at a time from cell F1

...

read more »



Craig,

Just a thought as the code works by it's self and all you want to do
is replicate the error must be the change you have made..
oPic.Top = AK1

try changing that to

oPic.Top = .Top

You never know..

Matthew
Replies Reply to this message
#4 Dave Peterson
July 21st, 2010 - 07:34 am ET | Report spam
Did you try the response you got on July 5?



On 07/20/2010 11:39, TheMilkGuy wrote:
Hi folks,

Based on the show/hide pictures information I read here:
http://www.mcgimpsey.com/excel/lookuppics.html

I edited it to work for my scenario. The first part is included
because I'm not sure if it is contributing to the problem, as my VB
knowledge is less than nil.

I get the Runtime Error 13: Type Mismatch error. Could someone
please tell me how I can fix it?

At the same time, if there is a way to 'clean up' the code, I would
appreciate that too.

Many thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula = False Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If

End Sub

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True
With Range("bo1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO2")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO4")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO6")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO7")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO8")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO9")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO10")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO11")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO12")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO13")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO14")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO15")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO16")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO17")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO18")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("BO19")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = AK1
oPic.Left = .Left
Exit For
End If
Next oPic
End With

End Sub



Dave Peterson
Replies Reply to this message
#5 TheMilkGuy
July 21st, 2010 - 07:43 am ET | Report spam
Per,

Thanks for the insight - If I click F8 to highlight each line through
the code (I assume that's what I'm supposed to do) I get the Type
Mismatch error at this line:

For Each oPic In Me.Pictures

AK1 is supposed to be the cell at which the pictures are displayed
atop one another. I tried changing that back to .Top but it did not
make any difference.

I hope this helps... I find it so frustrating that this code works in
my test workbook but falls to pieces in the document I need it in.

Cheers,
Craig

On Jul 20, 11:10 pm, Per Jessen wrote:
Hi

It is not easy to figure out why you get the 'Type Mismatch error',
without knowing which line is causing the error (Click debug and see
which line is highlighted!).
In the line:  oPic.Top = AK1, what is AK1 supposed to be? AK1 is seen
as a variable which has got no value, and it can cause the error. Try
to use the line below and see if it solve the problem:

oPic.Top = .Top

With your additional explanation, I think your code can be reduced to
the below.:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim rngPicture As Range

Set rngPicture = Range("BO1:BO19")
Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True
For Each cell In rngPicture.Cells
    With cell
        For Each oPic In Me.Pictures
            If oPic.Name = .Text Then
                oPic.Visible = True
                oPic.Top = AK1
                oPic.Left = .Left
                Exit For
            End If
        Next oPic
    End With
Next
End Sub

Regards,
Per

On 21 Jul., 03:38, TheMilkGuy wrote:

> Matthew,

> Your code is just like the website's code, which works fine, except
> that instead of one cell referencing one photo to display, I need the
> code to look at each cell from BO1 to BO19 and if the Picture # is
> listed in any of those cells, the image will display.  I am using
> transparent gif files, so they overlay without issue.

> For instance, if BO1 reads Picture 3, Picture 3 would display
> If BO2 is empty, no image appears
> If BO3 reads Picture 12, Picture 12 would display atop Picture 3
> ... et cetera

> I was hoping that getting this code working by simplifying it would
> get rid of the Type Mismatch error, would you agree?

> Thanks!
> Craig

> On Jul 20, 10:28 pm, Matthew wrote:

> > On 20 July, 17:39, TheMilkGuy wrote:

> > > Hi folks,

> > > Based on the show/hide pictures information I read here:http://www.mcgimpsey.com/excel/lookuppics.html

> > > I edited it to work for my scenario.  The first part is included
> > > because I'm not sure if it is contributing to the problem, as my VB
> > > knowledge is less than nil.

> > > I get the Runtime Error 13:  Type Mismatch error.  Could someone
> > > please tell me how I can fix it?

> > > At the same time, if there is a way to 'clean up' the code, I would
> > > appreciate that too.

> > > Many thanks!

> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.HasFormula = False Then
> > > Application.EnableEvents = False
> > > Target.Value = UCase(Target.Value)
> > > Application.EnableEvents = True
> > > End If

> > > End Sub

> > > Private Sub Worksheet_Calculate()
> > >         Dim oPic As Picture
> > >         Me.Pictures.Visible = False
> > >         Me.Pictures("Picture 1").Visible = True
> > >         With Range("bo1")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO2")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO3")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO4")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO5")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO6")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO7")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO8")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO9")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO10")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO11")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO12")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO13")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO14")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO15")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic
> > >         End With
> > >         With Range("BO16")
> > >             For Each oPic In Me.Pictures
> > >                 If oPic.Name = .Text Then
> > >                     oPic.Visible = True
> > >                     oPic.Top = AK1
> > >                     oPic.Left = .Left
> > >                     Exit For
> > >                 End If
> > >             Next oPic

...

read more »
Replies Reply to this message
Help Create a new topicNext page Replies Make a reply
Search Make your own search