Cell Shading from a Bit Map image

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave B - 15 Dec 2006 04:27 GMT

I have constructed a large data base of material samples and use the VLOOKUP
function to generate various forms and reports. I wish to include a bitmap
image on the form that changes with one of the variables. Simply put, if the
form is describing an apple, I'd like a picture of an apple, if describing a
pear, then a picture of a pear, and so on. I named the pictures and tried
bringing an image in with an IF statement but it only displayed the name I
assigned. Since there are only three bitmat images to display, I thought I
might accomplish this with conditional formating but I'm not sure how to turn
the image into a format shade. I'm kidda a "newby" at macro programming, but
I am willing to try it if it is the only solution.

Kindest Regards,

Signature

Dave B

Reply to this Message

Gord Dibben - 15 Dec 2006 17:30 GMT

Dave

Programmatically............see JE McGimpsey's site for a method of showing

hidden pictures based upon a value in a cell.

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

Gord Dibben MS Excel MVP

>I have constructed a large data base of material samples and use the VLOOKUP
>function to generate various forms and reports. I wish to include a bitmap
[quoted text clipped - 8 lines]
>
>Kindest Regards,

Gord Dibben MS Excel MVP

Reply to this Message

Dave B - 16 Dec 2006 02:10 GMT

Gord, Thanks for the link. This is exactly what I want to do. I downloaded
the sample and understand almost everything that is going on except.. where
are the pictures located. They aren't on a seperate sheet anywhere. They
have to be in the excel file, but I can find them.

If you are curious, you may wish to download the file. You will see what I
mean.

Anyway, I'll keep looking, thanks for your help

Best Regards,

Signature

Dave B

> Dave
>
[quoted text clipped - 20 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 16 Dec 2006 16:47 GMT

Dave

The pictures in JE's sample workbook are just hidden on the sheet.

From JE's site....................

The Worksheet_Calculation event is then used to place the picture on top of cell
F1, hiding the formula. The event macro is placed in the worksheet code module,
where it will fire each time a calculation occurs on the sheet. The macro hides
all the pictures, then displays the one corresponding to the value in F1,
repositioning it if necessary.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False 'all pictures get hidden
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True 'just the picture returned by the
'lookup formula
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

To see all the pictures change Me.Pictures.Visible = False to True

Gord

>Gord, Thanks for the link. This is exactly what I want to do. I downloaded
>the sample and understand almost everything that is going on except.. where
[quoted text clipped - 7 lines]
>
>Best Regards,

Gord Dibben MS Excel MVP

Reply to this Message

Dave B - 16 Dec 2006 19:23 GMT

Oh, I see that now in the macro (told you I was a "Newby" to VBA programming,
used to do lots of Lotus macros back in the 2.2 days, yes.. I'm old).

Anyway, help me understand just a couple of more points,

1) the command me.pictures.visible; is this a macro statement or is it a
name? I was not able to find the name me.picutures. If this is not a macro
command, how is the name generated?

2) is the stament oPic a macro statement? (basically the same question as
above)

3) I have only recently generated my first macro (and I used the record
feature to do it). It appears the event macros are entered using the VBA
editor. I am planning to enter the statements as they are presented in the
illustration. Is it possible then to add the recorded macro directly behind
the picture look up macro.

Thanks for your advice and patience!

Signature

Dave B

> Dave
>
[quoted text clipped - 41 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 16 Dec 2006 20:34 GMT

Dave

Assuming you have JE's sample workbook open in front of you.

Select Sheet1 and right-click on the sheet tab and "View Code"

You will find the event code in there.

That is where you would enter the event code in your workbook.

Now, to your questions............

1. Me.Pictures means Worksheet pictures. A Picture is an Object and Visible
is a property of that object. The line Me.Pictures.Visible = False says "hide
all the pictures on the sheet"

Me. is just another name for worksheet. Those are all the pictures you place
anywhere on the worksheet. I do mean anywhere.......

2. oPic is a variable which JE has named. Could have easily been named MyPic.
Just refers to whatever picture is assciated with the A2 DV dropdown selection
and returned by the VLOOKUP formula in F1

3. Sheet event code is generally entered into the sheet module. Recorded
macros are stored in a General Module in the workbook.

To open a General Module, with your workbook open, hit ALT + F11 to open the
Visual Basic Editor.

Select your workbook/project and expand to Modules. Double-click on a module to
open and see your recorded code.

Not sure why you would wish to place your recorded amcro behind the picture
lookup macro.

Please elaborate.

Gord

>Oh, I see that now in the macro (told you I was a "Newby" to VBA programming,
>used to do lots of Lotus macros back in the 2.2 days, yes.. I'm old).
[quoted text clipped - 15 lines]
>
>Thanks for your advice and patience!

Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 16 Dec 2006 22:57 GMT

Addenda.........

For more on learning about macros see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Also his pages on Events

http://www.mvps.org/dmcritchie/excel/event.htm

Chip Pearson also has some info on Events at his site.

http://www.cpearson.com/excel/events.htm

Gord

>Dave
>
[quoted text clipped - 54 lines]
>>
>>Thanks for your advice and patience!

Reply to this Message

Tanya - 15 May 2008 03:41 GMT

Hello Gord

I have been reading this thread with interest, and basically trying to
achieve the same outcome.

My question is, how do you associate the name picture 1 with the object?

Thanking you in advance.

Kind Regards
Tanya

> Dave
>
[quoted text clipped - 56 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 16 May 2008 00:49 GMT

When you create or insert an object Excel gives it a name.

In somewhat haphazard manner, I might add. I just inserted a clipart picture
and Excel named it Picture 88

To re-name an Object from Picture 88 to Picture 1 just select the object.

You will see Picture 88 in the Name Box to left of Formula Bar.

Simply type in a new name......Picture 1

Gord

>Hello Gord
>
[quoted text clipped - 68 lines]
>>
>> Gord Dibben MS Excel MVP

Reply to this Message

Tanya - 16 May 2008 07:10 GMT

Ohh so simple... Thank you again, greatly appreciated.

cheers
Tanya

> When you create or insert an object Excel gives it a name.
>
[quoted text clipped - 81 lines]
> >>
> >> Gord Dibben MS Excel MVP

Reply to this Message