Can this be done?

[RJC]

In F2, I have a Data Validation list comprising
Stocks
Bank
Investments.

Can I link a picture to each of these selections so that when I select each one, the picture changes to visually reflect the selection.

Purely cosmetic, but it would be nice to do.

Is it possible and if so, how would it be done.

Any thoughts!

Thanks

 

[bat17]

See if this article fits your needs.

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

Peter

 

[RJC]

Thanks Peter,
Exactly what I was after. Much obliged.
Regards,
Rick

See if this article fits your needs.

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

Peter

 

[RJC]

Hello Peter,

I've been successful in applying the guide from McGimpsey & Associates as per your reply. The test works ok when I use a new workbook and all is fine.

However, when I try to apply it to my live worksheet it is crashing at the line in the code that starts "For Each oPic In Me.Pictures". The error code is 13 and the msg is Type Mismatch. I have some other code in the worksheet code module and it appears to be tripping over this as my SelectionChange code now also fails.

Below is the code in my WorkSheet. If you have the time could you help me establish what is wrong. Have I done something wrong in inserting the code in the worksheet module.

Would appreciate your assistance.
Thanks, Peter
Regards,
Rick


This is all the code I have in the worksheet module.

'Private Sub ComboBox4_Change()
Option Explicit
Private Sub ComboBox4_Change()
Me.ComboBox4.Value = Format(Me.ComboBox4.Value, "00.00")
End Sub

Private Sub ComboBox5_Change()
'Me.ComboBox5.Value = Format(Me.ComboBox5.Value, "00.00")
End Sub

Private Sub ComboBox6_Change()

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 10 And Target.Row > 11 And Target.Row < 24 Then
Range("J11").Value = Target.Value
End If
End Sub


Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = True
With Range("F2")
For Each oPic In Me.Pictures 'Crashing Here....type mismatch
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

See if this article fits your needs.

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

Peter

 

[Chitosunday]

Please check if the range f2 is where your vlookup formula should be.

The code should be:

Dim oPic As Shape
Me.Pictures.Visible = False
With Range("f2")
For Each oPic In Me.Shapes
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With

 

[Chitosunday]

or just change the code "Dim oPic As Picture" to
"Dim oPic as object"

this is because picture object is the old object which was replaced by shape object. However, picture code is still accepted for backward compatibility. The only thing not accepted is when you declare it as picture. It should be object for general use.

 

[RJC]

Hello Chitosunday,
Thanks for your reply.

You correctly picked up my error in F2. My picture is to be displayed in C1 and I have altered the code accordingly. Changing Picture to Shape makes no difference. Still crashing at the same spot.

This is what I have now:-
In C1 is =VLookup(F2,PicTable,2,False)
PicTable is a named range in Y21:Z23.
In F2 is my dropdown cell which has 3 entries representing the 3 pictures.
This dropdown is merged across F2:H2. (tried unmerging to just have F2 as the cell but no difference).
My pictures are just placed beside each other on the spreadsheet.

When I choose an option in F2, the result in C1 is text display of Picture 1,2 or 3 so the named range appears to be working ok.

Can you suggest anything else I may be doing incorrectly. For instance, to get the code into the worksheet module, I just double clicked the sheet and copied in the macro I had created elsewhere. Is this ok to do. It must be working as the macro will hide the pictures before crashing. I also don't quite understand how Excel associates the pictures. I just inserted 3 pictures at a point on the sheet. Does Excel just name them Picture 1,2 and 3 in the order they are inserted on the sheet?

I just can't see why it does not work as I have successfully done this on a new blank sheet and it works perfectly. Must be something amiss though.

By the way. Can I change the Me.Pictures.Visible = False line to True so that I can see at a glance what pictures I have. If I leave it at False then other tables on my sheet disappear also. It has to be True to make these appear. Does this impact on the macro operation.

Any other thoughts.

Thanks and regards,
Rick

Please check if the range f2 is where your vlookup formula should be.

The code should be:

Dim oPic As Shape
Me.Pictures.Visible = False
With Range("f2")
For Each oPic In Me.Shapes
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With

 

[RJC]

Changed code to Object and this stopped the crashing.

However, when I select an option from the dropdown, cell C1 changes to display text of "Picture 1,2 or 3". No pictures!

Also I have Me.Pictures.Visible set to True. If I set it to False then other list boxes on my sheet disappear. Does this matter?

I've now hit the wall with this. Works fine if I start with a new sheet but won't work when I try to incorporate the same setup in my main sheet.

or just change the code "Dim oPic As Picture" to
"Dim oPic as object"

this is because picture object is the old object which was replaced by shape object. However, picture code is still accepted for backward compatibility. The only thing not accepted is when you declare it as picture. It should be object for general use.

 

[Chitosunday]

You need to set each specific picture.visible to false because if you made it to true, nothing will happen. Can you email me your file at monsunday1959@yahoo.com so i can debug your file.

 

[Chitosunday]

One more thing, the default name when you inserted a picture with no specific name before is object 1 and the next one inserted is object 2 and not picture 1, picture 2.

To check the actual name, put a message box like "msgbox opic.name" after cide
"For Each oPic In Me.Shapes "