Hello I have a spreadsheet in excel and I want to acomplish a task. I
have a sheet that has a few cells that I want in a pie graph "Cell
AX25, AO28 and AT26". I want to have on my sheet a button called
"Delay-Total Footage-%" that would run a macro to give me this pie
chart. Thing is sometimes I change the numbers on the graph which would
give me different numbers in "Cell AX25, AO28 and AT26" so if I hit the
button I would need it to update the pie graph to the current number in
those cells. Also each week I start a ner sheet that would have the
same cells needed but diferent info added I would need each sheet to
run thier own macro for that sheet and so on. The sheets are named
"Week Ending 1-7-7, Week Ending 1-14-7 and so on" Please help Im not
very good with macros so it would have to be explained....

Thanks,
Chad

oxicottin wrote:
Hello I have a spreadsheet in excel and I want to acomplish a task. I
have a sheet that has a few cells that I want in a pie graph "Cell
AX25, AO28 and AT26". I want to have on my sheet a button called
"Delay-Total Footage-%" that would run a macro to give me this pie
chart. Thing is sometimes I change the numbers on the graph which would
give me different numbers in "Cell AX25, AO28 and AT26" so if I hit the
button I would need it to update the pie graph to the current number in
those cells. Also each week I start a ner sheet that would have the
same cells needed but diferent info added I would need each sheet to
run thier own macro for that sheet and so on. The sheets are named
"Week Ending 1-7-7, Week Ending 1-14-7 and so on" Please help Im not
very good with macros so it would have to be explained....

Thanks,
Chad

Hi Chad,

I don't think you need a macro, the chart automatically updates
whenever any of the cell's values are changed.
All you need to do is set the chart up.

To set the chart up follow these steps...

1. Select AX25
2. Insert|Chart...|Chart Type: Pie|Next|Series
3. The Values: box will show the address of AX25 preceded by an equals
sign, the Sheet name and an exclamation mark. Click in the box and type
a comma immediately to the right of AX25, then click on AO28. Type
another comma then click on AT26. Then click Finish.

You didn't mention any cells on your sheet that contain the pie chart's
categories labels. If you have labels to include, then on the "Series"
tab sheet of the "Chart Wizard Step 2 of 4" dialog, click in the
Category Labels: box, then click in the cell with AX25's Category
label, type a comma, then repeat for AO28 and AT26. If there aren't any
cells on the sheet with the Category Label names you can type them
directly into the Category Labels: box. No speech marks needed, just
separate the three labels with commas, XL does the rest.

Ken Johnson

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


Ken Johnson wrote:
oxicottin wrote:
Hello I have a spreadsheet in excel and I want to acomplish a task. I
have a sheet that has a few cells that I want in a pie graph "Cell
AX25, AO28 and AT26". I want to have on my sheet a button called
"Delay-Total Footage-%" that would run a macro to give me this pie
chart. Thing is sometimes I change the numbers on the graph which would
give me different numbers in "Cell AX25, AO28 and AT26" so if I hit the
button I would need it to update the pie graph to the current number in
those cells. Also each week I start a ner sheet that would have the
same cells needed but diferent info added I would need each sheet to
run thier own macro for that sheet and so on. The sheets are named
"Week Ending 1-7-7, Week Ending 1-14-7 and so on" Please help Im not
very good with macros so it would have to be explained....

Thanks,
Chad

Hi Chad,

I don't think you need a macro, the chart automatically updates
whenever any of the cell's values are changed.
All you need to do is set the chart up.

To set the chart up follow these steps...

1. Select AX25
2. Insert|Chart...|Chart Type: Pie|Next|Series
3. The Values: box will show the address of AX25 preceded by an equals
sign, the Sheet name and an exclamation mark. Click in the box and type
a comma immediately to the right of AX25, then click on AO28. Type
another comma then click on AT26. Then click Finish.

You didn't mention any cells on your sheet that contain the pie chart's
categories labels. If you have labels to include, then on the "Series"
tab sheet of the "Chart Wizard Step 2 of 4" dialog, click in the
Category Labels: box, then click in the cell with AX25's Category
label, type a comma, then repeat for AO28 and AT26. If there aren't any
cells on the sheet with the Category Label names you can type them
directly into the Category Labels: box. No speech marks needed, just
separate the three labels with commas, XL does the rest.

Ken Johnson

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

Ken,
Once again thanks for replying and all the help it is very helpfull!!
I use this excel sheet at work an d I will try to do what you explained
in your reply here at work today. I will let you know the results later
this evening. Sorry about the picture it looked ok when I set it up. I
wanted to make a .xls file with one sheet in it for an example and you
could have downloaded it but I figured you wouldnt have downloaded it.
I still could if you want to see the .xls!

Thnaks again,
Chad


Ken Johnson wrote:
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

oxicottin wrote:
Ken,
Once again thanks for replying and all the help it is very helpfull!!
I use this excel sheet at work an d I will try to do what you explained
in your reply here at work today. I will let you know the results later
this evening. Sorry about the picture it looked ok when I set it up. I
wanted to make a .xls file with one sheet in it for an example and you
could have downloaded it but I figured you wouldnt have downloaded it.
I still could if you want to see the .xls!

Thnaks again,
Chad


Hi Chad,

Feel free to email me a copy and I'll see what I can do.

Ken Johnson

How do I find your email address?


Ken Johnson wrote:
oxicottin wrote:
Ken,
Once again thanks for replying and all the help it is very helpfull!!
I use this excel sheet at work an d I will try to do what you explained
in your reply here at work today. I will let you know the results later
this evening. Sorry about the picture it looked ok when I set it up. I
wanted to make a .xls file with one sheet in it for an example and you
could have downloaded it but I figured you wouldnt have downloaded it.
I still could if you want to see the .xls!

Thnaks again,
Chad


Hi Chad,

Feel free to email me a copy and I'll see what I can do.

Ken Johnson

Sorry never mind it was right in front of me!

oxicottin wrote:
How do I find your email address?


Ken Johnson wrote:
oxicottin wrote:
Ken,
Once again thanks for replying and all the help it is very helpfull!!
I use this excel sheet at work an d I will try to do what you explained
in your reply here at work today. I will let you know the results later
this evening. Sorry about the picture it looked ok when I set it up. I
wanted to make a .xls file with one sheet in it for an example and you
could have downloaded it but I figured you wouldnt have downloaded it.
I still could if you want to see the .xls!

Thnaks again,
Chad


Hi Chad,

Feel free to email me a copy and I'll see what I can do.

Ken Johnson

oxicottin wrote:
Sorry never mind it was right in front of me!


Hi Chad,

I haven't received anything yet.
How's it going?

Ken Johnson

Sorry I got busy at work but I sent it today!


Ken Johnson wrote:
oxicottin wrote:
Sorry never mind it was right in front of me!


Hi Chad,

I haven't received anything yet.
How's it going?

Ken Johnson