Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Workbook Close - For EXPERTS Only!

  1. #11
    Board Regular
    Join Date
    Apr 2002
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #12
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    _______________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-23 08:01 ]

  3. #13
    Board Regular
    Join Date
    Apr 2002
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •