prevent automatic execution of a macro

[macrofan]

I have a command button which is assigned to a macro for a click. However, whenever I open the workbook this macro is automatically executed. But this is not what I want. I want this macro to run only when I click the button. How can I prevent the automatic execution when I open Excel?

Thanks.

 

[Ruddles]

You can't prevent the automatic execution of the macro when you open Excel but you can prevent the automatic execution of the macro when you open the workbook itself by holding the Shift key down until it's finished opening.

Or if it's a permanent thing, you can modify the Workbook_Open event handler so that it doesn't run the code: press Alt-F11 to get into the VB editor, then Ctrl-R to open the Project Explorer, double-click ThisWorkbook and make the necessary changes. If you don't know how to do this, post the code here (between code tage - the # icon in the advanced editor toolbar) and we'll talk you through it.

 

[Ruddles]

*between code tags

 

[macrofan]

Ruddles, thanks for your reply.

Is this what you meant?

Private Sub Workbook_Open()

End Sub

I have a macro with a name of "Test_Click"

 

[tusharm]

Only code in specifically named subroutines in specific modules is executed automatically. If you have a subroutine named XYZ in a standard module, there's no way Excel would automatically run it.

So, what is your subroutine named and what module is it in?

I have a command button which is assigned to a macro for a click. However, whenever I open the workbook this macro is automatically executed. But this is not what I want. I want this macro to run only when I click the button. How can I prevent the automatic execution when I open Excel?

Thanks.

 

[macrofan]

Only code in specifically named subroutines in specific modules is executed automatically. If you have a subroutine named XYZ in a standard module, there's no way Excel would automatically run it.

So, what is your subroutine named and what module is it in?

I am a beginner with VBA, so that I am not sure I get your comment fully.

I placed the "TestButton" module in the general modeule category, should I put in class module?

 

[Ruddles]

Private Sub Workbook_Open()

End Sub

If there's nothing between those lines, nothing should run automatically when the workbook is opened. What makes you think something is running?

@Tushar, could it be something in Personal.xls? (I should know more about this but I never use it.)

 

[macrofan]

If there's nothing between those lines, nothing should run automatically when the workbook is opened. What makes you think something is running?

@Tushar, could it be something in Personal.xls? (I should know more about this but I never use it.)

I have this procedure (TestButton) to do some calculation based on original data. Everytime when I open the workbook, the newly calculated columes appear in addition to the columes of original data.

 

[Ruddles]

Try this: insert a line at the top of that macro - immediately after the Sub line - as follows:-

MsgBox "Macro is running"

Run the macro manually to confirm the message box appears.

Now close and re-open the workbook. Do you get the message box?

 

[macrofan]

And if you delete that macro, then save and re-open the workbook, that doesn't happen?

If I delete them, and save and reopen the file,

hah, hah, hah,

the columes disappear, and when I click the button for calculation, they reappear again.

Thank you and Tushar for your time, patience and your help.

You guys have a wonderful long weekend!