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,

--
Dave B

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

On Thu, 14 Dec 2006 20:27:00 -0800, Dave B
wrote:

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,

Gord Dibben MS Excel MVP

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,
--
Dave B


"Gord Dibben" wrote:

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

On Thu, 14 Dec 2006 20:27:00 -0800, Dave B
wrote:

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,

Gord Dibben MS Excel MVP

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!
--
Dave B


"Gord Dibben" wrote:

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
On Fri, 15 Dec 2006 18:10:00 -0800, Dave B
wrote:

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,

Gord Dibben MS Excel MVP

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

On Sat, 16 Dec 2006 11:23:02 -0800, Dave B
wrote:

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!

Gord Dibben MS Excel MVP

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

On Sat, 16 Dec 2006 12:34:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Sat, 16 Dec 2006 11:23:02 -0800, Dave B
wrote:

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!

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

"Gord Dibben" wrote:

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

On Sat, 16 Dec 2006 11:23:02 -0800, Dave B
wrote:

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!

Gord Dibben MS Excel MVP

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

On Wed, 14 May 2008 19:41:01 -0700, Tanya
wrote:

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

"Gord Dibben" wrote:

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

On Sat, 16 Dec 2006 11:23:02 -0800, Dave B
wrote:

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!

Gord Dibben MS Excel MVP

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

cheers
Tanya

"Gord Dibben" wrote:

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

On Wed, 14 May 2008 19:41:01 -0700, Tanya
wrote:

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

"Gord Dibben" wrote:

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

On Sat, 16 Dec 2006 11:23:02 -0800, Dave B
wrote:

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!

Gord Dibben MS Excel MVP