• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

How to stop a macro Excel 5.0

I 've got a Auto_open macro in a sheet. I open another sheet of the same type (with the same Auto_open macro). Then I try to close the first one. At the moment I use the second one, the first one is reopened, presumably to execute its macro. How to prevent that ? I tried to do an "End" command, but to no avail ...
0
phl
Asked:
phl
  • 3
  • 2
  • 2
  • +1
1 Solution
 
carttiCommented:
Can you clarify your question ? This is how I understood it. You have an Auto_Open macro in one Workbook. In the code of that macro, you open another workbook. You then close the first workbook. From when you say 'At the moment I use the second on....' I've lost you.

Is there only one macro which is in the first workbook, or are there more ?
0
 
hammertsCommented:
Try opening the sheet with the SHIFT key held down, this will not run any auto-open macros , you can then edit anything.
0
 
phlAuthor Commented:
In fact I want the macro to stop by itself, with no human intervention. More information is needed i see :) :
Each WORKBOOK I use contains the same macros (Auto_open and others). Thess macros are :
Sub Auto_open()
    Application.OnSheetActivate = "Init"
End Sub

and calls :

Sub Init()
    Application.OnEntry = "NameOfSub"
End Sub

(...)

Then each time I enter a value, the "NameOfSub" macro is used. But I would like that each workbook uses its own macro instead of using the macro belonging to the first opened workbook.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
carttiCommented:
Use this syntax to avoid Excel being confused as to what macro to use:

Application.OnEntry = name of workbook file & "!NameOfSub"
0
 
carttiCommented:
Sorry, add the name of the module to the syntax to be certain. So...

Application.OnEntry = name of workbook file & "!" & name of module & ".NameOfSub"
0
 
vboukharCommented:
You have just use more clear description of your sub:
Sub Auto_open()
Application.OnSheetActivate = ActiveWorkbook.Name & "!Init"
End Sub

Sub Init()
Application.OnEntry = ActiveWorkbook.Name & "!NameOfSub"
End Sub
Check it!
0
 
vboukharCommented:
Sorry, I've not seen cartti answer.
0
 
phlAuthor Commented:
Thanks cartti ! (and vboukhar too :) ) . See you later ... :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now