autostart macro problems

[Firefly]

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?

 

[earlyd]

When it runs from the menu, why would it run the Autpen - isn't it already open?

 

[Firefly]

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.

 

[earlyd]

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 Autpen routine - because the workbook is already open. I guess maybe I don't understand your question.

 

[earlyd]

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.

 

[Firefly]

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
End


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

I appreciate any help you can offer - just want to open excel, process the macro RecTime automatically & then close excel. Thanks

 

[earlyd]

(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.