oxicottin wrote:
Ken, thanks for replying I have been trying to figure this out for over
a week! But I should have explained this better. I have an .xls file
that has several sheets in it and each sheet is named diferently
EXAMPLE: Week ending 1-7-7, Week Ending 1-14-7 and so on. On each sheet
I want to add 3 buttons so I can bring up a pie chart with totals from
selected cells on that sheet only. Button 1 will include cells
AT26,AX25 and AO28. Button 2 will include cells AT49, AX48 and AO51.
Button 3 will include cells AT72,AX71 and AO74. When I make a new sheet
I just copy to end and date it for the next week. Here is a screen
shot of what I got but, I couldnt get the whole sheet in the picture
just part.
http://i54.photobucket.com/albums/g9...cel_PrtScn.jpg

Thanks,
Chad



Hi Chad,

Could you possibly do it this way...

1. Set up your 3 Pie Charts.

2. Name them Pie Chart 1, Pie Chart 2 and Pie Chart 3 by...
a) From the main menu bar View|Toolbars|Drawing to get the Drawing
Toolbar visible
b) Click on the "Select Object" button (arrow)
c) Click on the first pie chart.
d) Click in the Name box on the left side of the Formula Bar
e) Type "Pie Chart 1" (without the speech marks)
f) Press Enter (important step I often forget, resulting in no name
change!)
g) Repeat steps c) to f ) for the two other charts, using "Pie Chart
2" and "Pie Chart 3" for their new names. (Make sure to get those
names exactly right, the code relies on it)

3. Add the following macros to a general VBA code module in the
workbook by...
a) Pressing Alt + F11 to get into the VBA Editor, then from the VBA
main menu bar Insert|Module.
b) Copy these macros...

Public Sub ToggleChart1()
Dim shpChart1 As Shape
Set shpChart1 = ActiveSheet.Shapes("Pie Chart 1")
shpChart1.Visible = Not shpChart1.Visible
Select Case shpChart1.Visible
Case True
ActiveSheet.Shapes(Application.Caller). _
TextFrame.Characters(1, 4).Text = "Hide"
Case False
ActiveSheet.Shapes(Application.Caller). _
TextFrame.Characters(1, 4).Text = "Show"
End Select
End Sub

Public Sub ToggleChart2()
Dim shpChart2 As Shape
Set shpChart2 = ActiveSheet.Shapes("Pie Chart 2")
shpChart2.Visible = Not shpChart2.Visible
Select Case shpChart2.Visible
Case True
ActiveSheet.Shapes(Application.Caller). _
TextFrame.Characters(1, 4).Text = "Hide"
Case False
ActiveSheet.Shapes(Application.Caller). _
TextFrame.Characters(1, 4).Text = "Show"
End Select
End Sub
Public Sub ToggleChart3()
Dim shpChart3 As Shape
Set shpChart3 = ActiveSheet.Shapes("Pie Chart 3")
shpChart3.Visible = Not shpChart3.Visible
Select Case shpChart3.Visible
Case True
ActiveSheet.Shapes(Application.Caller). _
TextFrame.Characters(1, 4).Text = "Hide"
Case False
ActiveSheet.Shapes(Application.Caller). _
TextFrame.Characters(1, 4).Text = "Show"
End Select
End Sub

c) Paste them into the code module.
d) Press Alt + F11 to return to the user interface.

3. Add 3 Buttons from the Forms Toolbar by...
a) From the main menu bar View|Toolbars|Forms to view the Forms Toolbar
b) Click on the Forms Toolbar's Button icon then click on the sheet.
c) Assign ToggleChart1 macro
d) Repeat steps b) and c), assigning ToggleChart2 and ToggleChart3
macros respectively

4. Right click Button 1 then select "Edit text" and change its
caption to "Show Pie Chart 1" (You can use any alternative caption,
just make sure that it starts off with "Show", the code will change
those four characters to "Hide" when the Chart is visible.). Repeat
for Buttons 2 and 3.


When you need a new sheet, right click the old sheet's tab and select
"Move or Copy...", choose an appropriate position in the "Before
sheet:" textbox, place a tick in the "Create a copy" check box, click
OK, change the copy's name on its sheet tab.

There are probably other ways of doing it, it's just that coding charts
can be very difficult (particularly for me!)
If you have any trouble I can always email you a workbook with the
buttons and charts in place. Not the same places you are wanting
though, that image you posted was pretty bad (fuzzy).

Ken Johnson