Results 1 to 11 of 11
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    prevent workbook from closing (Excel 2003/SP1)

    I'm working with a contractor who has xl97 and I have xl2003 on a project for work. When I send him the .xls file I always protect the code but since he has an earlier version of Excel he can't unprotect it. Since he's not very familiar with the VBIDE and only records macros, I wanted to ensure that he doesn't forget to protect the VBA project. I wrote this code but it always closes the workbook no matter what.
    <pre> If Application.VBE.ActiveVBProject.Protection = 0 Then
    MsgBox "This VBA Project has not been protected. Please return" & _
    vbCrLf & "to the editor (Ctrl+F11) and protect the project" & _
    vbCrLf & "with the appropriate password.", vbCritical + vbOKOnly, _
    "VBA Project is not Protected"
    Cancel = True
    Else
    ' do other stuff at normal workbook close
    End If</pre>

    The workbook continues to get closed whether I set Close to False or True. I want to abort the Close event and leave the .xls file open. How else can I do this?

    Thnx, Deb

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    LIke this:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.VBProject.Protection = 0 Then
    Cancel = True
    Else
    'Whatever
    End If
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    I just now tried what you suggested (I never used "me" in this way for events) and the workbook still closed. This should be easy, but something is missing here. I attached a simplistic workbook, one sheet, and code at workbook close only. It still closes.

    Any ideas why? I don't have other versions of Excel to test it on.

    Deb

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    Your code will not let me close the workbook on XL2K. Are you getting the message box before the workbook closes?
    Legare Coleman

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    Yes I get the message box just fine, I hit OK and the workbook closes. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> I've even stepped through the code and nothing else interrupts it.

    Since it works for you with xl2000, maybe I shouldn't worry since this is just a temporary fix and the contractor I'm working with has xl97 so if it works in xl2000 maybe it'll also work in xl97. Very weird though that it does nothing, maybe the MSKB has something to say about it. Since I'm the one who will be maintaining it when the contractor is gone, I'll be removing that code but it does concern me why such a simple thing doesn't work anymore.

    Thnx,
    Deb

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    You might want to take a look at <!post=This Post,290455>This Post<!/post>, and see if one of Jan Karel's suggestions help. Particularly the ones that have to do with Regserver and XLStart.
    Legare Coleman

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    Ok I went through the entire list of possible fixes for Excel-isms and nothing worked. I have no Personals.xls, no .xlb files, nothing in XLSTART, and no alternate path. I only had one add-in loaded (Name Manager) and I deleted that. I get the message that the project isn't locked (my msgbox in the code) and Cancel = true, but it still closes the workbook. I couldn't find anything in the MSKB about this being a new bug (although we know lots of things aren't mentioned there).

    Does anyone else have xl2003 to try this? Maybe I'll just go ahead and send this workbook as is to the contractor since he has xl97 and this code will probably work. It's just a temporary reminder for him and since I'll be maintaining the .xls file after he leaves, I'll remove the code later.

    Thanks anyway,
    Deb

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    Works fine on my Excel 2003 11.7113.5703 and on my Excel XP.

    Have you tried putting the code in a brand new workbook?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    Odd it works in your xl2003. yes I did put the code in a new workbook - that was the one I had attached in a previous post to this same thread. My xl2003 is 11.6355.6360 SP1.

    Actually I just heard from the contractor who has xl97 and it doesn't work for him very well. The problem is that even though he had re-protected the VBA project, at workbook close my code doesn't recognize it as protected so he can't close it. In my tests with xl2003, I used the immediate window and typed
    <pre>?application.VBE.ActiveVBProject.Protection </pre>

    and got 0 each time even after I protected the workbook and saved it (but not exit). So it seems to not be much use if I have to close the workbook before this .Protection is set to 1.

    Fortunately it's not a big deal, I just wanted a way to remind the contractor to protect the code before he sends the .xls file off to anyone.

    Thanks anyway,
    Deb

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    I guess the protection is only true when the project hasn't been "opened" by typing in the password or when a protected workbook is freshly opened???
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: prevent workbook from closing (Excel 2003/SP1)

    You're right. I tried it with Word VBA and it only returns 1 if the ActiveVBProject isn't opened (with the password).

Posting Permissions

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