Open another workbook while closing the active workbook

[Karthikeyan Devan]

Hi All,

My team is working on one tracker where at the end of the day they have to update it on the consolidate workbook. The problem here is people missing to update in the consolidate tracker.

So here what I need is that, when user close the active workbook then the consolidate workbook has to be opened automatically.

For example, If i am working on Karthik_tracker.xls and when i close this workbook another workbook should openend which i have saved it as consolidate_tracker.xls.

Any help on this would be very much appreciated!!!

 

[repairman615]

Hello,

Press Alt + F11
Press keystrokes Alt - I - M
Paste in the following to the module:

<font face=Courier New><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> WorkbookOpen(WorkBookName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">' returns TRUE if the workbook is open</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    WorkbookOpen = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> WorkBookNotOpen<br>    <SPAN style="color:#00007F">If</SPAN> Len(Application.Workbooks(WorkBookName).Name) > 0 <SPAN style="color:#00007F">Then</SPAN><br>        WorkbookOpen = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>WorkBookNotOpen:<br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Now find ThisWorkBook from the tree at left
Double Click to bring up ThisWorkBook Code
Past in the following

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> WorkbookOpen("consolidate_tracker.xls") <SPAN style="color:#00007F">Then</SPAN> Workbooks.Open ("C:\YOUR FULL FILE PATH\consolidate_tracker.xls")<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Your full file path will need to go where specified.

Now when you close the workbook with this code, the second workbook should open.

 

[Warship]

just like repairman said.
He was much more detailed than what i was posting

 

[Karthikeyan Devan]

Wow!! Really perfect!! Thanks a lot...

A small help on this same macro. Before the provided macro run a Msgbox should display stating "Please update consolidate tracker".

I mean, when a user try to close the active workbook, a msgbox should display stating: "Please update consolidate workbook". Once user click on ok command button in msgbox, automatically the other workbook should open!!!

I am really satisfied with what you have provided but even the Msgbox get succeed it would be a really great help!!

Appreciate your help!!!

 

[repairman615]

This adds the msgbox:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><br>MsgBox "Please update consolidate workbook", vbOKOnly, "Update Consolidate Tracker."<br><br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> WorkbookOpen("consolidate_tracker.xls") <SPAN style="color:#00007F">Then</SPAN> Workbooks.Open ("C:\YOUR FULL FILE PATH\consolidate_tracker.xls")<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Karthikeyan Devan]

Excellent!!! Thanks a lot