• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Toggle button with a picture

nagovind

Member
Dear all !


Greetings


In a excel sheet (not in userform) is it possible to have a toggle button with a picture/ image

so that if i click a toggle button one image has to displayed if i click the same image other image has to be displayed. Or with any means it is possible to achieve this ?. Please guide
 

Luke M

Excel Ninja
Multi-step process, but this is how you could get something like that...


1. Somewhere in yourworkbook, put all your pictures, sized to the cell they are in (hold Ctrl to "snap")

2. Define a cell somewhere as KeyCell, give it a value of 1

3. Define a named range, using a formula like:

=OFFSET(Cell_above_first_picture,KeyCell,0)

Call this formula "MyPic"

4. Draw a rectangle where you want the picture displayed. Edit the formula bar to say:

=MyPic

5. Using the forms toolbar, create either a spinner wheel or scroll wheel. Define the min/max parameters as appropriate, create a cell link to the KeyCell.


Done! Now, when you change the spinner/scroll wheel, it should cause the picture link to change as well. Note that the change only occurs after you release the mouse button (no holding down the mouse and seeing all the images)
 

nagovind

Member
Luke M


thanks for the reply but nothing is working for me please guide / eloborate

something is happening by no pictures are changing

Is it possible to upload a example file in any one of the below site please


rapidshare.com

4shared.com

megaupload.com
 

Luke M

Excel Ninja
Debra's created something similar for weather pics. Link to file:

http://www.contextures.com/Weather_Pics.zip


Link to templates page:

http://www.contextures.com/excelfiles.html#Function
 

Hui

Excel Ninja
Staff member
Nagovind


This works well for me


Add a Rectangle to your worksheet

Link it to the following code

edit File names & locations as appropriate

[pre]
Code:
Sub Toggle_Pics()

Dim add As String
Dim Pic1 As String, Pic2 As String

Pic1 = "C:UsersIanPicturesWalls1.jpg" 'Change my name and Location
Pic2 = "C:UsersIanPicturesWalls2.jpg" 'Change my name and Location
add = "$A$1"  'Change to suit

With ActiveSheet.Shapes("Rectangle 1")
If Range(add).Value = 1 Then
.Fill.UserPicture Pic1
Else
.Fill.UserPicture Pic2
End If
End With

Range(add).Value = -1 * Range(add).Value

End Sub
[/pre]
 

nagovind

Member
Hui


Its amazing


But is it possible to move one step ahead..by keeping the image file inside the excel file itself ? please refer to the below attachment. Using this code ...can the same can be modified to use the image file inside the Excel file itself ? please help


https://rapidshare.com/files/460602360/Changing_Excel_Icon_on_the_title_bar_of_the_Excel_application.pdf
 

Hui

Excel Ninja
Staff member
Not sure what your code is trying to achieve

but with 2 pictures you can use some simple code to toggle between them

With 2 pictures, Picture 1 and Picture 2

assign Show_Pic1 to one and Show_Pic2 to the other

Put the pictures on top of each other

Click away

[pre]
Code:
Sub Show_Pic1()
ActiveSheet.Shapes("Picture 1").Visible = True
ActiveSheet.Shapes("Picture 2").Visible = False
End Sub

Sub Show_Pic2()
ActiveSheet.Shapes("Picture 1").Visible = False
ActiveSheet.Shapes("Picture 2").Visible = True
End Sub
[/pre]
 

nagovind

Member
Hui !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


It is amzingggggggggggggg


There is no equivalent for you


Its Working


thanks a lot
 
Top