Displaying a picture by pushing CommandButton

Hi,

I am relatively new at Excel VBA, but have used Access VBA in the past
and need a few tips to do the following seemingly simple task.

I have a spreadsheet, with a comand button cmdShowPic. When the user
clicks on cmdShowPic, it must show a picture that I specify.

My question is: how do I implement this? To start, do I simply copy and
paste the pic into the spreadsheet? And how do I caal a method that
will show the pic? I am not sure how to do this, maybe with a User
Form?

Thanks in advance for the help!

J

Hello Jean,

You can place an Image Control on the Worksheet. Afterwards right click the Image Control and select Properties. Change the AutoSize Property to True. Close the Properties Window. Right Click the CommandButton and select View Code.

Place the Following Code in the Command Button's Click Event

Image1.Picture = stdole.LoadPicture("C:\My Documents\Picture 1.jpg")

This will display all the common picture files as well as icons and cursors.

Hope this helps,
Leith Ross

If you already have it on the sheet in the position you want:

Private Sub cmdShowPic_Click()
With ActiveSheet.Pictures(1)
.Visible = Not .Visible
End With
End Sub

this was for a picture entered in the sheet using Insert=>Picture (from
file).

If you need to do that, turn on the macro recorder while you do it manually
(tools=>Macros => Record a new macro, insert the picture, then click the
stop recording button, or if it is not visible Tools=>Macros=>Stop
Recording. Then you can examine the code recorded and generalize it if
necessary).

--
Regards,
Tom Ogilvy


"Jean" <jeanjanssens@hotmail.com> wrote in message
news:1108298438.346666.146100@g14g2000cwa.googlegroups.com...
> Hi,
>
> I am relatively new at Excel VBA, but have used Access VBA in the past
> and need a few tips to do the following seemingly simple task.
>
> I have a spreadsheet, with a comand button cmdShowPic. When the user
> clicks on cmdShowPic, it must show a picture that I specify.
>
> My question is: how do I implement this? To start, do I simply copy and
> paste the pic into the spreadsheet? And how do I caal a method that
> will show the pic? I am not sure how to do this, maybe with a User
> Form?
>
> Thanks in advance for the help!
>
> J
>

I like the idea of a popup userform with the picture on it, and it is not too
hard to do: First, create the userform and size it however you like. You
don't need to add any controls on it, because you can use the form's own
background to display the picture. Then, create the button and assign this
code to it:
With UserForm1
.Picture = LoadPicture(Sheets("Sheet1").Range("A1").Value)
.Show
.Picture = LoadPicture ' with no arguments, this clears the current picture
End With

You can set the userform to a fixed size (in which case I suggest you set
the picturesizemode to Zoom) or, if necessary, you could find out how big the
picture is and scale the form accordingly, but that gets messy so I won't
attempt that here! But hope it gives you a start, at least.

"Jean" wrote:

> Hi,
>
> I am relatively new at Excel VBA, but have used Access VBA in the past
> and need a few tips to do the following seemingly simple task.
>
> I have a spreadsheet, with a comand button cmdShowPic. When the user
> clicks on cmdShowPic, it must show a picture that I specify.
>
> My question is: how do I implement this? To start, do I simply copy and
> paste the pic into the spreadsheet? And how do I caal a method that
> will show the pic? I am not sure how to do this, maybe with a User
> Form?
>
> Thanks in advance for the help!
>
> J
>
>

Should have specified, but should be obvious: I had my picture path stored in
the cell A1 of sheet "Sheet1" - but you can use any text string here so just
supply your file path to the picture file as the parameter for the
LoadPicture function.

"K Dales" wrote:

> I like the idea of a popup userform with the picture on it, and it is not too
> hard to do: First, create the userform and size it however you like. You
> don't need to add any controls on it, because you can use the form's own
> background to display the picture. Then, create the button and assign this
> code to it:
> With UserForm1
> .Picture = LoadPicture(Sheets("Sheet1").Range("A1").Value)
> .Show
> .Picture = LoadPicture ' with no arguments, this clears the current picture
> End With
>
> You can set the userform to a fixed size (in which case I suggest you set
> the picturesizemode to Zoom) or, if necessary, you could find out how big the
> picture is and scale the form accordingly, but that gets messy so I won't
> attempt that here! But hope it gives you a start, at least.
>
> "Jean" wrote:
>
> > Hi,
> >
> > I am relatively new at Excel VBA, but have used Access VBA in the past
> > and need a few tips to do the following seemingly simple task.
> >
> > I have a spreadsheet, with a comand button cmdShowPic. When the user
> > clicks on cmdShowPic, it must show a picture that I specify.
> >
> > My question is: how do I implement this? To start, do I simply copy and
> > paste the pic into the spreadsheet? And how do I caal a method that
> > will show the pic? I am not sure how to do this, maybe with a User
> > Form?
> >
> > Thanks in advance for the help!
> >
> > J
> >
> >

Thanks to both of you!

I showed both methods to a client, and he will decide on his own which
one to use. Both solutions were easy enough for them to take care of.

Regards,
Jean