Create a VBA button to open an image in worksheet

[gary2014]

Hi!

(Sub-Create a button to open an image in worksheet)

I've been asked to have an image (small jpg file) appear and disappear in an Excel worksheet that is being used as a form. In other words, they want the user to click on a Command Button which will display an image, then give him/her the option to click the button again and the image disappears (They didn't specifically ask for a button, but I guess a button is the best option based on what I read on other threads in this forum, however if there is an alternative, that would work for me too!).

I have no experience with VBA, and I wasn't able to find a similar example to my situation in this forum, where I could copy and paste the code in VB.

	A	B	C	D
1	Command Button 1 (Click here Mobile Phone pic)	The Mobile Phone pic should display here
2	Command Button 2 (Click here Head Phone pic)	The Head Phone pic should display here
3	Command Button 3 (Click here Land Phone pic)	The Land Phone pic should display here
4

<tbody>
</tbody>

The Mobile phone pic is already in this same worksheet (Cell B90)
The Head phone pic is already in this same worksheet (Cell B113)
The Land phone pic is already in this same worksheet (Cell B135)

Please revert back if I am not clear

 

[VBA Geek]

Select the Picture and change the name via the Name Box (left of Formula bar).

 

[gary2014]

Hey........
Excellent................

It's working now!!!

I Saulte your knowledge

 

[gary2014]

Hi VBA Geek,

I am able to create three toggle buttons

1. Mobile Phone
2. Head Phone
3. Cordless Phone

Case 1: When I click on "Mobile Phone" toggle button, the image appears, and when I re-click the button the image disappears (this is perfect).

Case 2: When I click on "Head Phone" toggle button, the image does not appear.
It appears only when I keep the Mobile Phone image without clearing it.

Case 3: When I click on "Cordless Phone" toggle button, the image does not appear.
It appears only when I keep the Mobile Phone image without clearing it.

Now if I have all the three images visible on my screen.

If I want to clear "Head Phone" & "Cordless Phone" the toggle button doesn't work.
Firstly, I have to clear the Mobile Phone image then I am able to clear the remaining two.

Please give me a code to fit the below three requirements:
1. I would request you to give me a code by which toggle buttons are independent.
2. When I click on "Mobile Phone" button the image should appear, likewise when I click on either of remaining two buttons the first image should disappear and next image should appear.
3. Only one image should appear at any given point of time.

 

[VBA Geek]

I think 3 option button would do the job for you.

you can get the excel file as well: Zippyshare.com - Pic.xlsb

Cheers

Option Explicit


Private Sub optCordlessPhone_Click()
    With Me
        .Pictures("Mobile Phone").Visible = .optMobilePhone.Value
        .Pictures("Head Phone").Visible = .optHeadPhone.Value
        .Pictures("Cordless Phone").Visible = .optCordlessPhone.Value
    End With
End Sub


Private Sub optHeadPhone_Click()
    With Me
        .Pictures("Mobile Phone").Visible = .optMobilePhone.Value
        .Pictures("Head Phone").Visible = .optHeadPhone.Value
        .Pictures("Cordless Phone").Visible = .optCordlessPhone.Value
    End With
End Sub


Private Sub optMobilePhone_Click()
    With Me
        .Pictures("Mobile Phone").Visible = .optMobilePhone.Value
        .Pictures("Head Phone").Visible = .optHeadPhone.Value
        .Pictures("Cordless Phone").Visible = .optCordlessPhone.Value
    End With
End Sub

 

[gary2014]

Hi <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Geek,

You are genius!
This is what exactly I looking for.

You've mentioned "Option Explicit".

Could you please guide me on how to access this "Option Explicit"?

Is this something like " Option button (ActiveX control)"?

Please guide me through this.

 

[VBA Geek]

Option Explicit should be the very first line of each module you have, it forces you to declare each variable you use thus making your code more efficient

Declaring Variables

Hi <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Geek,

You are genius!
This is what exactly I looking for.

You've mentioned "Option Explicit".

Could you please guide me on how to access this "Option Explicit"?

Is this something like " Option button (ActiveX control)"?

Please guide me through this.

 

[gary2014]

Okay.
To start with, when I go to the Developer tab > click on Insert > which ActiveX option shall I choose?

 

[VBA Geek]

they're Option Buttons

 

[gary2014]

Hi Geek,

When I use the above code it works fine.

Now, I already have a Reset Button (VBA Code) in that sheet.
The code stats with

Private Sub CommandButton1_Click()
Range("B2:B15").SpecialCells(xlCellTypeConstants).ClearContents


End Sub

When I try to enter your code for image display, I am being asked to write the code below the Reset Button VBA Code.

After I finish the code, the image works fine for one or two attempts after which I face the following problem.

1. The font of Mobile Phone in first option button decreases drastically with every click on this button.
2. The font of Head Phone & Cordelss Phone along with option buttons increases drastically with every click on these buttons.

Need your help in accomplishing this task.

Requirements: I want "Reset/Clear" button and "Option" buttons to function on the same page.

 

[VBA Geek]

do u have a copy of your workbook