Runtime Error 13 and a really ugly code...

T

TheMilkGuy

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
 
T

TheMilkGuy

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
 
P

Per Jessen

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
 
M

Matthew

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





...

read more »
Click to expand...

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
 
D

Dave Peterson

Did you try the response you got on July 5?



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
Click to expand...
 
T

TheMilkGuy

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
 
T

TheMilkGuy

Did you try the response you got on July 5?

Dave Peterson
Click to expand...

Sorry Dave, can't seem to find any responses other than the link to
McGimpsey & Associates on July 2. Perhaps it's because I use this
group via Google Groups on my browser, but if you think it would be
helpful I would surely appreciate a copy & paste.

Cheers!
 
D

Dave Peterson

You changed the question (slightly):

First, you write that you're looking at F1:F15, but your code finishes with F16.
I used F1:F16.

Second, you have a line: oPic.Top = F1
I'm guessing that you meant the top of each cell.

I didn't test this, but it compiled ok:

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("F1:F16")

Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True

For Each myCell In myRng.Cells
With myCell
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
Next myCell

End Sub
 
T

TheMilkGuy

Dave,

I like the code - it does look simpler.

I still get get the Type Mismatch error, but now instead of just an
"Ok" dialog box it gives me a Debug option which brings me to the "For
Each oPic In Me.Pictures" line.

HOWEVER... I just remembered that I am running a similar code in
another worksheet for a single cell - it has 6 different pictures that
appear based on its contents and its code looks like this:

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

Would the reason I'm getting Type Mismatch be because I'm using
Me.Pictures in two sheets of the same workbook? Is there a
workaround?

Thank you!
C
 
D

Dave Peterson

The code in the other worksheet module shouldn't be a problem here.

Is the line that gives the error the "Me.Pictures.visible = false" line?

How many pictures do you have on the worksheet that has the trouble? I've seen
errors (not mismatch errors, though), when this kind of code was used and there
were lots and lots and lots of those objects on that sheet.

And did you by chance declare any variables and call them Pictures or do you
have any procedure/function/module names named Pictures?
 
T

TheMilkGuy

Hi Dave,

The line that's highlighted when I hit Debug is: For Each oPic In
Me.Pictures

I can't see anything in the worksheet called Pictures.

There are 15 pictures. This is what is so frustrating because the
code works in my test worksheet but copy and pasting gives me this
same error...

C
 
D

Dave Peterson

I don't have another guess.

You may want to try putting the same code into a new worksheet in a new workbook
(add some pictures to that test worksheet) to see what happens.

If it works, maybe it's time to recreate the real worksheet????
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Show/Hide with VB... Need tailoring. 1
Limit on Pictures? 13
vba newbie need help. 2
macro picture setting 1
Linked pictures to cell dropdown 0
Runtime Error 1004 Unable to set the top property of the picture c 1
debugging runtime error 1004 0
Runtime Error 1004 5

Top