Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File | Close on a workbook (Excel 2KPro, O2KPro, Win2KPro)

    this one might also be easy but I'm having a hard time finding the event to hook a macro on in the Object Browser.

    What I am trying to do is, if a user does File | Close and there are changes to the workbook, Excel pops up a dialog asking if you want to save changes with [YES] [NO] [CANCEL] buttons. What I want to do is, if the user selects [YES] to fire a macro that saves the file using some custom file name code. (if there are no changes the file closes, which is fine; if there are changes and the user doesn't want to save them, fine as well; only if they do want to save changes do I want to insert my version of the Save As... dialogue rather than the default)

    TIA!!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File | Close on a workbook (Excel 2KPro, O2KPro, Win2KPro)

    Something like the following place in the ThisWorkbook code module shoul dwork :

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Saved Then Exit Sub
    Select Case MsgBox("Do you want to save changes ? ", _
    vbYesNoCancel + _
    vbCritical + _
    vbDefaultButton3) ' <font color=448800>set Cancel as the default </font color=448800>
    Case vbCancel
    Cancel = True
    Case vbYes
    ' Call custom macro to save with new name
    Me.Saved = true
    Case vbNo
    Me.Saved = True
    End Select
    End Sub

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File | Close on a workbook (Excel 2KPro, O2KPro, Win2KPro)

    funny - i got your response mere seconds after implementing and testing very much the same code. thanks anyway!

Posting Permissions

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