Excel -- stop execution of workbook_open ?

[wotgoesup]

I have timesheets with a Workbook_Open macro that asks if it's a new week. If it is, it adds 7 days to a cell and uses that as part of the file name for the save. Works fine, each member of the team has their own wkbk, and the date is the same in each.
I copy those 3 into one workbook with a summary sheet. This has the same date cell, and the VBA builds the file name and opens the required workbook for the team members, but when it tries to copy into the summary workbook, the open macro for each time sheet gets executed.

i.e. the summary VBA triggers the "new week?" msgbox in each timesheet it opens.
How do I stop / bypass the msgbox when executing the summary VBA?

summary has Workbooks.Open filename:= .
which opens each timesheet,
they have

"Private Sub Workbook_Open()"


a = MsgBox("Start a New Week?", vbYesNo)
If a = vbYes Then
...

ActiveWorkbook.SaveAs filename:= _
("G:\time\" & efiletxt)
End If

THANKS!

 

[RonRepp]

Why not use a linked workbook?

I don't know if it's practical; just a thought.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[wotgoesup]

Ron -
Thanks for the idea, but there's another condition:
The manager may not want the updated timesheets - he may want what was there last time he opened it. So I have a msgbox that asks if you want to update the sheets.
If I link, I think it will update every time it's opened.