Worksheet Open Event

**muckem333** · 01-27-2010, 04:51 PM

Hello,

I am trying to run a macro each time a specific worksheet is opened.

My worksheet is named 'Quote' and the macro I want to call is contained in a module. The macro is Update_quote().

What would the code be? And where would I place the code?

**Richard Buttrey** · 01-27-2010, 04:55 PM

Hi,

When you say worksheet do you really mean worksheet, or workbook? I ask since it's usual to Activate a worksheet and open a workbook.

You need to use either the Worksheet_Activate event or the Workbook_Open event and put the Update_quote macro name in there.

Rgds

**muckem333** · 01-27-2010, 04:58 PM

Awesome. That was simple enough!

Thanks

**muckem333** · 01-27-2010, 05:15 PM

Richard,

I have the Worksheet Activate working. Now I have another issue:

I have an 'export' button which creates a copy of the worksheet in another workbook. Now, I am getting a "compile error: Sub or Function not defined" when I run the macro associated with the 'export' button.

In this 'exported' copy I do not want the Worksheet_Activate code to copy over. Is there a way to either delete it or de-activate it when I create the copy?

**Richard Buttrey** · 01-27-2010, 06:56 PM

Hi,

You can delete VBA code with

Please Login or Register  to view this content.

this same subject was covered in a similar thread a couple of days ago. See http://www.excelforum.com/excel-prog...n-copying.html

HTH

**muckem333** · 01-28-2010, 09:54 AM

Still getting the same error. Is this because when I copy the worksheet it is automatically activated, and hence, the first step it goes through is to call the worksheet_activate() macro? Calling this macro as soon as it is activated will call the Update_Quote() macro which will no longer be linked to appropriate references and cause the error. So where would I insert the delete VBA code? Or is there some way to get around this, like an if statement or Onerror?