Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    London, Gtr London, England
    Thanked 0 Times in 0 Posts

    Close routine, cancel close and return to spreadsheet

    Dear loungers,

    If I have a close event and offer the user the opportunity to cancel (the close) how do i quit the routine and stop the close to return to the spreadsheet? I want to use vbYesNoCancel where Yes will do update a date, save and exit, No will save & exit and Cancel will return to the spreadsheet.

    Thank you....................... liz
    Last edited by lizat; 2012-11-12 at 09:46.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,455 Times in 1,324 Posts

    Here's some code I use in Excel to trap the big red X and abort the close if the standard menubar is not visible.
    Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
       With Application
           If Not .CommandBars("Worksheet Menu Bar").Enabled Then
             MsgBox "Please use the Menu's to Exit the System" & vbCrLf & _
                    "Closing using the X will cause serious" & vbCrLf & _
                    "system problems!", vbOKOnly + vbCritical, _
                    "Error: Improper Attempt to Exit System"
             Cancel = True
           End If
       End With
    End Sub
    You should be able to adapt it to your needs. The trick is setting the value of the value of Cancel to True if you don't want to proceed.
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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