Hello

I have a workbook with about 45 charts in it. I have set up a menu so that
users can select the chart they want to view by clicking on a hyperlink that
takes them to the location within the worksheet where the chart is located.

At the bottom of each chart I have two macro buttons: one to return the user
back to the menu, and one to print all charts in the worksheet. What I want
to be able to do is add a third macro button which will allow the user to
print only the chart they're currently viewing on the screen.

Is there a way to do this without creating 45 separate macros (one for each
chart number)?

I was thinking there would be a way to have a macro activate the chart
window based on a predetermined set of co-ordinates (such as the middle of
the screen) to select the chart?

Any ideas how I can do this?

Joe.
--
If you can measure it, you can improve it!
--
If you can measure it, you can improve it!

Hi,

I'm sure there's a much more sophisticated approach but try this:

Sub PrintOneChart()
x = Application.Caller
mySpace = InStr(1, x, " ")
x = Mid(x, mySpace + 1, 2)
ActiveSheet.ChartObjects("Chart " & x).Activate

...
End Sub

Check the charts name by holding down the Ctrl key and clicking it. The
name will appear in the Name Box - something like Chart 1. Assuming you are
using the Form toolbar buttons, and note its name in the Name Box, then edit
its name to read Button 1, to match the number of the chart. You need to
leave a space between the number and the object name.

You assign this macro to all of the buttons.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP


"Monomeeth" wrote:

Hello

I have a workbook with about 45 charts in it. I have set up a menu so that
users can select the chart they want to view by clicking on a hyperlink that
takes them to the location within the worksheet where the chart is located.

At the bottom of each chart I have two macro buttons: one to return the user
back to the menu, and one to print all charts in the worksheet. What I want
to be able to do is add a third macro button which will allow the user to
print only the chart they're currently viewing on the screen.

Is there a way to do this without creating 45 separate macros (one for each
chart number)?

I was thinking there would be a way to have a macro activate the chart
window based on a predetermined set of co-ordinates (such as the middle of
the screen) to select the chart?

Any ideas how I can do this?

Joe.
--
If you can measure it, you can improve it!
--
If you can measure it, you can improve it!