Run Macro when opening another sheet

Posted by Brian S. on December 11, 2001 8:22 AM

I got such great help from Tom Urtis & Bill Robbins that I'm back!

I have a sheet that comes from another data source. I have a macro that I would like to have run automatically when the person opens the sheet. How do I do this? I currently have the macro saved as another excel file.

Ie..i get a file named capacity from another data source, when I open this sheet I would like to run capacitymacro which is contained in sheet2.

Thanks,
Brian S.

Posted by Tom Urtis on December 11, 2001 9:00 AM

Hi Brian, glad I was able to help.
Regarding your request, this might be as easy as right-clicking on the sheet you are referring to, then left click on View Code and paste in:

Private Sub Worksheet_Activate()
'YOUR MACRO CODE GOES HERE
End Sub

Just insert your macro code where I've indicated, and you should be good to go.

If this is not what you want, please repost.

Tom Urtis

Posted by Brian on December 11, 2001 9:32 AM

Tom-
I get a new sheet every other day and was looking for something to do this automatically, didn't know if can base the macro off the sheet name, so I open the macro sheet, it opens the sheet I want to modify, runs the macro on this sheet, and then closes the macro sheet?

I'm probably going to be distributing so I don't want others to view the code.

Thanks!

Brian

Posted by Tom Urtis on December 11, 2001 10:10 AM

It sounds do-able but I'm still a little unclear about which sheet is which ("new sheet", "macro sheet", "modify sheet", etc.). As an option just to save space on the board, you can email me with your code and a step by step process that you would like to have automated, with the file names and sheets identified, and I'll be happy to help you find a solution if I can.

Tom U.