I have a workbook with a couple charts that for some reason "shrink" each
time the file is saved -- some kind of 2007 vs. 2003 problem. To solve it I
just wrote a wee macro that resizes the chart to the original, desired
dimensions. I attached it to the worksheet's Activate event, which works
fine once the workbook is open and you move to that worksheet from some other
worksheet. But it DOESN'T run when the workbook first opens; if that
worksheet is the first one to appear, it has the shrunken chart, and I have
to switch to a different worksheet and then back to that one in order to kick
off the macro.

In Access, where I do most of my work, I would use the form's Load or Open
event, but if there's a comparable Worksheet event in Excel I haven't found
it. Anyone know how I can make my macro run as soon as that sheet opens? (I
thought about the run-on-startup idea, but the worksheet in question may or
may not be the first one to appear when the file is first opened, and if it
isn't, the macro would presumably fail with a "Chart? What chart?" error.)

In ThisWorkbook module there are workbook level events. Right click the XL
icon to the left of file on the main menu. select View Code. This takes you
directly into ThisWorkbook. Change from the drop down just above the code
window from General to Workbook. Now you can select the events from the drop
down next to it.
You want the open event.
--
HTH...

Jim Thomlinson


"LarryP" wrote:

I have a workbook with a couple charts that for some reason "shrink" each
time the file is saved -- some kind of 2007 vs. 2003 problem. To solve it I
just wrote a wee macro that resizes the chart to the original, desired
dimensions. I attached it to the worksheet's Activate event, which works
fine once the workbook is open and you move to that worksheet from some other
worksheet. But it DOESN'T run when the workbook first opens; if that
worksheet is the first one to appear, it has the shrunken chart, and I have
to switch to a different worksheet and then back to that one in order to kick
off the macro.

In Access, where I do most of my work, I would use the form's Load or Open
event, but if there's a comparable Worksheet event in Excel I haven't found
it. Anyone know how I can make my macro run as soon as that sheet opens? (I
thought about the run-on-startup idea, but the worksheet in question may or
may not be the first one to appear when the file is first opened, and if it
isn't, the macro would presumably fail with a "Chart? What chart?" error.)

Workbook_Open event in Thisworkbook module will run when workbook is opened.

Do away with the Sheet's Activate event code.

Private Sub Workbook_Open()
code to resize the chart on the sheet
End Sub

Or put your resize code into a macro then call the macro.

Private Sub Workbook_Open()
MyMacro
End Sub


Gord Dibben MS Excel MVP

On Wed, 7 Apr 2010 11:33:02 -0700, LarryP
wrote:

I have a workbook with a couple charts that for some reason "shrink" each
time the file is saved -- some kind of 2007 vs. 2003 problem. To solve it I
just wrote a wee macro that resizes the chart to the original, desired
dimensions. I attached it to the worksheet's Activate event, which works
fine once the workbook is open and you move to that worksheet from some other
worksheet. But it DOESN'T run when the workbook first opens; if that
worksheet is the first one to appear, it has the shrunken chart, and I have
to switch to a different worksheet and then back to that one in order to kick
off the macro.

In Access, where I do most of my work, I would use the form's Load or Open
event, but if there's a comparable Worksheet event in Excel I haven't found
it. Anyone know how I can make my macro run as soon as that sheet opens? (I
thought about the run-on-startup idea, but the worksheet in question may or
may not be the first one to appear when the file is first opened, and if it
isn't, the macro would presumably fail with a "Chart? What chart?" error.)