Type Mismatch

[Steve_in_Kent]

I'm trying to use an example in excel, of how to use vba to amend a picture depending on a value.

The example is given here:-

http://www.mcgimpsey.com/excel/lookuppics.html

I have downloaded the example and it works fine. But when i try to use it, in my own sheet, i get a type mismatch on the highlighted line.

In the example, cell F1 is used to place the VLOOKUP, i am using H26

The Vlookup i have in H26 is:-

=VLOOKUP(U26, PicTable, 2, FALSE)
(My lookup table is fine)

And correctly displays '' Picture 5 ''

the VBA Code is thus :-

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

The highlighted line is causing the type mismatch error.

[StuartR]

Can we check that Excel sees the things in your workbook as pictures.

If you go to the immediate window and type
? ActiveWorkbook.ActiveSheet.Pictures.Count
what does it show?
How about
? ActiveWorkbook.ActiveSheet.Pictures(1).Name

One other thing to try. What happens if you replace
Dim oPic as Picture
with
Dim oPic as Object

[Steve_in_Kent]

changing the .picture, to .Object gives another error.

From the Activewindow:-

? ActiveWorkbook.ActiveSheet.Pictures.Count
9

I have some other pictures on the sheet, so i guess thats whats causing this

? ActiveWorkbook.ActiveSheet.Pictures(1).Name
Object 1
? ActiveWorkbook.ActiveSheet.Pictures(2).Name
Picture 1
? ActiveWorkbook.ActiveSheet.Pictures(3).Name
Picture 2
? ActiveWorkbook.ActiveSheet.Pictures(4).Name
Picture 3
? ActiveWorkbook.ActiveSheet.Pictures(5).Name
Picture 4
? ActiveWorkbook.ActiveSheet.Pictures(6).Name
Picture 5
? ActiveWorkbook.ActiveSheet.Pictures(7).Name
Picture 6
? ActiveWorkbook.ActiveSheet.Pictures(8).Name
Picture 7
? ActiveWorkbook.ActiveSheet.Pictures(9).Name
Picture 8

By removing the object, the code now works. (The object was just a picture, .. of some strange type)....

Many thanks for the tip !

[StuartR]

That's a great result.

Out of interest, what kind of object was it?

[Steve_in_Kent]

Stuart hi.. i think it was a .png

it was being reffered to as an object. rather than a picture.