I have a worksheet with 2 charts on it. Previously, each chart was driven by a range of data that was just below the chart. I’ve since changed those ranges of data to actual TABLES (so it’s easier to add or remove rows and have these changes automatically be reflected in the chart).
But since I’ve done this, each of my two macros for printing the charts produce results I don’t want.
The way these macros work is they do the following:
1. Unprotect the worksheet
2. Select the chart to be printed
3. Take you to print preview (where I’ve already set up page numbering, headers and footers, landscape orientation, etc.)
4. Let you execute the print operation from the print preview page (one that gets called by the macro, not the Windows 7 backstage print preview)
5. After you’re done printing (or NOT printing, just backing out), re-protects the worksheet so you are back to where you started.
The macros both look like this (they each select a different chart):
ActiveSheet.Unprotect Password:="<password>"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.Protect Password:="<password>"
Previously, when you ran this macro, a print preview screen came up (not the one you see in Windows 7 backstage) showing just the chart in landscape mode including my pre-set headers and footers/orientation, etc. The user just had to click PRINT and they'd get the print set up popup and go from there.
BUT I've since changed the range of data under the chart to an actual TABLE, when I run my print macro, I get portrait, no headers and footers, and the table as well as the chart. I have a workaround— don’t use the macro — just select the chart and FILE->PRINT, but I’m not sure how to change the macro so it does what I want. I can add in the header and footer stuff and change the orientation manually (and maybe Excel will remember these settings for the next time I print this chart), but I can't figure out how to ELIMINATE the accompanying table-- I just want to print the CHART. My end users like the macros (I have them on the screen as push-buttons, so I need to fix the macros-- I can't tell them to print manually.
And using the recorder to build the macro doesn't help much once you are in the print preview or print set up screens-- that stuff doesn't get recorded and doesn't give you a way to end the macro recording either.
Any ideas?
But since I’ve done this, each of my two macros for printing the charts produce results I don’t want.
The way these macros work is they do the following:
1. Unprotect the worksheet
2. Select the chart to be printed
3. Take you to print preview (where I’ve already set up page numbering, headers and footers, landscape orientation, etc.)
4. Let you execute the print operation from the print preview page (one that gets called by the macro, not the Windows 7 backstage print preview)
5. After you’re done printing (or NOT printing, just backing out), re-protects the worksheet so you are back to where you started.
The macros both look like this (they each select a different chart):
ActiveSheet.Unprotect Password:="<password>"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.Protect Password:="<password>"
Previously, when you ran this macro, a print preview screen came up (not the one you see in Windows 7 backstage) showing just the chart in landscape mode including my pre-set headers and footers/orientation, etc. The user just had to click PRINT and they'd get the print set up popup and go from there.
BUT I've since changed the range of data under the chart to an actual TABLE, when I run my print macro, I get portrait, no headers and footers, and the table as well as the chart. I have a workaround— don’t use the macro — just select the chart and FILE->PRINT, but I’m not sure how to change the macro so it does what I want. I can add in the header and footer stuff and change the orientation manually (and maybe Excel will remember these settings for the next time I print this chart), but I can't figure out how to ELIMINATE the accompanying table-- I just want to print the CHART. My end users like the macros (I have them on the screen as push-buttons, so I need to fix the macros-- I can't tell them to print manually.
And using the recorder to build the macro doesn't help much once you are in the print preview or print set up screens-- that stuff doesn't get recorded and doesn't give you a way to end the macro recording either.
Any ideas?