Workbook Close - For EXPERTS Only!

[Michael Pettinicchi]

I say for "For EXPERTS Only" because to-date, nobody has been able to figure this one out and those who tried have given up and never replied to my replies to their replies.

I have the following code in the "Workbook" of "ThisWorkbook". It works if I open the file directly, i.e., it does the job and closes. But, if I open the file via another Excel file, it does the job but doesn't close. What's wrong?

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
Range("a5").Select
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ThisWorkbook.Close
End Sub

Thanks
Michael

 

[Michael Pettinicchi]

I've tried Tim's suggestion of putting the close in another Function and another Sub and call these but that doesn't work either.

Also interesting is that I placed a MsgBox before and after the Close and I get both messages but the Close is completely ignored - just as if it were a comment.

Instead of testifying, I think Billy should go back to his office and fix his code.

 

[Mark O'Brien]

On 2002-04-22 10:01, Michael Pettinicchi wrote:
Ok you guys. You've got me.

I've tried everything you said (I think) and it DOES NOT WORK!

Here is exactly what I've got.

In WK1.XLS, Sheet 1, I have a button which when clicked executes the following from the Sheet 1 VBA code which is:

Private Sub CommandButton1_Click()
Workbooks.Open "WK2.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

In WKB.XLS, Sheet 1 VBA code, I have:

Public Sub CloseBook()
Application.EnableCancelKey = xlDisabled
Range("a5").Select
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ThisWorkbook.Close
End Sub

In WKB.XLS, ThisWorkbook VBA code, I have:

Private Sub Workbook_Open()
CloseBook
End Sub

I believe this is exactly as Mark said and all I get when executing WKB gets opened is a compile error message "Sub or Function not defined."

I tried Tim's way, but it doesn't work. I don't get an error message but WK2.XLS wont close automatically. What I might be missing is this "Auto" business. Am I supposed to place my sub in a special place. I tried it in Sheet 1 VBA and in ThisWorkbook VBA pages.

Best regards from
EXCELently Frustrated Michael

I used your "open" code from above to open a workbook that contains your original code in the "Open" event. It works perfectly. Perhaps the the reason the problem has not been solved to your satisfaction is because we are trying to solve the wrong problem.

_______________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-23 08:01

 

[Michael Pettinicchi]

EUREKA!

I have not found the problem but I have found a way around it.

I placed "ThisWorkbook.Close" under "Workbook_SheetDeactivate" in "ThisWorkbook" and in the "Workbook_Open" sub, after doing the job I go to Sheet 2 which causes the deactivation of Sheet 1 and the Close gets executed. The code is:

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
Range("a5").Select
Application.CommandBars("Worksheet Menu Bar").Enabled = True
SendKeys "^{PGDN}"
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
ThisWorkbook.Close
End Sub

Many thanks for your help.
Michael

Quote of the day: "Never say die!"