How do I conditionally insert an image (.bmp file)?

D

Defoes Right Boot

I need to show a bitmap only if (for example) cell A1 = 1 but not if A1 = 0.

Is this possible using conditional formatting or other method?

Thanks
 
B

Bernie Deitrick

"Defoes Right Boot"

First off, you should post with a name. It's only polite to let us know who
you are.

You can use the worksheet's change (if A1 is directly entered) or calculate
event (if A1 contains a formula) to hide or show the bitmap (change the
visible property to either true or false) based on the value in A1. Copy the
first procedure, right-click on the worksheet tab, select "View Code" and
paste into the window that appears. Copy the second procedure, and paste
into a standard codemodule. Change the file and path, and you can lose the
MsgBoxes as well.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
HandleBMP
Application.EnableEvents = True
End If
End Sub

Sub HandleBMP()
Dim myCell As Range
Set myCell = Selection

On Error Resume Next
If Range("A1").Value = 1 Then
MsgBox "Inserting"
ActiveSheet.Shapes("A1 Picture").Delete
Range("B1").Select
ActiveSheet.Pictures.Insert( _
"C:\Path\Filename.BMP").Select
Selection.Name = "A1 Picture"
Else
MsgBox "Deleting"
ActiveSheet.Shapes("A1 Picture").Delete
End If

myCell.Select
End Sub
 
M

M John

Mr. Deitrick,
I have a similar question (at least I think it just builds on the previous
post below):

I would like to have a macro that inserts one of 5 symbols to a cell based
on the value in the cell. What I'm aiming for is something similar to the
tables Conumer Reports uses to rate the items they evaluate: Red circle,
half full (upper half) red circle, empty circle, black half full (lower)
circle, and black full circle.

I have the images are ready to go and the code below will work for inserting
an image for a single "if" statement, but I'm not getting the (I'm guessing)
nested statements correct.

Any help you can provide will be most appreciated.

Many thanks in advance,
M John
 
A

ATang

Dear Bernie,
I had a similar question earlier and had benmarked what you recommended. It
was very useful and we were able to add in different bmp files based on
different scenarios. However, my next problem is - if this worksheet is
protected, the bitmap file doesn't change according to the condition. Since
the intention is just to allow user clicking a particular cell for choosing
the company name, then the logo is popped up automatically, we don't want to
open up the rest of the spreadsheet. Here below is what we've put and
appreciate your advice on how could we fix this problem:
********************************************
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$6" Then
Application.EnableEvents = False
HandleBMP
Application.EnableEvents = True
End If
End Sub

Sub HandleBMP()
Dim myCell As Range
Set myCell = Selection

On Error Resume Next

If Range("G6").Value = "Vendor" Then
ActiveSheet.Shapes("B1 Picture").Delete
ActiveSheet.Shapes("A1 Picture").Delete
Range("A1").Select
ActiveSheet.Pictures.Insert( _
"P:\Merchandising\GSL Logo Picture.bmp").Select
Selection.Name = "A1 Picture"
Else

ActiveSheet.Shapes("A1 Picture").Delete
End If

If Range("G6").Value = "Customer" Then
ActiveSheet.Shapes("B1 Picture").Delete
ActiveSheet.Shapes("A1 Picture").Delete
Range("A1").Select
ActiveSheet.Pictures.Insert( _
"P:\Merchandising\Solito Logo Picture.bmp").Select
Selection.Name = "B1 Picture"
Else

ActiveSheet.Shapes("B1 Picture").Delete
End If

myCell.Select
End Sub
********************************************

Thank you very much,
ATang
 
Top