autostart macro problems


New Member
Mar 27, 2002
I've set up a macro to run as a custom menu item on a custom menu in excel. It opens a workbook & performs various functions, saves the info & then closes the workbooks (including the macro workbook).

I also set up this macro to autostart when the workbook is opened, which is how I get it to run from the desktop (it opens excel, the workbook, etc.) This was accomplished by using:

Private Sub Workbook_Open()
My Macro
End Sub

From the desktop it runs fine. However, running it from the menu in Excel causes it a real problem - it runs, closes, & tries to run again, but the macro file is closed & I get an error. Is there a conditional statement I should put in the Private Sub that will recognize that it's being run from the menu as opposed to the desk top, so that it will terminate the autoopen macro prior to the MyMacro statement?

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
When it runs from the menu, why would it run the Auto_Open - isn't it already open?
Upvote 0
I understand - my question is that I want excel to recognize when it's run from the menu (& bypass) the autostart, and when it runs from the desktop, where autostart is required. I think I need a piece of conditional code in the autostart routine, but I don't know what it is. :(
Upvote 0
Does run from the menu mean the workbook in question is already open and you are running a macro off a menu inside it somewhere. The macro will not run the Auto_Open routine - because the workbook is already open. I guess maybe I don't understand your question. :confused:
Upvote 0
Not sure I totally understand your situation but could you have your menu driven routine call another macro which updates a cell value in your workbook then calls the original macro? Then the original macro could check that cell, reset it to normal. and bypass whatever it needs to bypass.

the cell would not be updated when run from the desktop.
Upvote 0
I wanted the versatility to be able to start a macro from the desktop, where excel is not currently open, or from a custom menu item, when excel is open already (but not the workbooks the macro works with).

I set up a custom menu item to open workbook Timesheet & run the
macro "RecTime". It updates the worksheet Time.xls & then closes both Time.xls & Timesheet.xls (which terminates the macro). Works, no problem.

I set up a shortcut on the desktop to the workbook LogIn.xls which has an auto open macro in the ThisWorkbook area. This shortcut is supposed to open excel, open the LogIn.xls workbook, which automatically initiates the macro stored in Timesheet.xls, "RecTime". My problem comes when I try to close all the workbooks. I can close everything through "RecTime" except the LogIn.xls workbook. I've tried Application.Quit, a separate sub to close LogIn.xls located within one of its modules, etc. Below is the code I've been working with:

Located in This Workbook in LogIn.xls:
Private Sub Workbook_Open()
Workbooks.Open "i:\my documents\timesheet.xls"
Application.Run ("timesheet.xls!rectime")
End Sub

I also tried just putting my macroname in place of the workbooks & application line - did the same thing.

End of RecTime macro located in module in Timesheet.xls:
Windows("time.xls").Close False
If erm = "M" Then Application.Run ("login.xls!Workbook_Close")
Windows("lTimesheet.xls").Close False

Sub Workbook_Close()
Workbooks("timesheet.XLS").Close SaveChanges:=False
Workbooks("i:\my documents\logIn.XLS").Close SaveChanges:=False
End Sub

I appreciate any help you can offer - just want to open excel, process the macro RecTime automatically & then close excel. Thanks
Upvote 0
(but not the workbooks the macro works with).

That's what I didn't realize. I thought you were in the workbook with the macro.
Upvote 0

Forum statistics

Latest member
Vishal Gupta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back