Glad I could be of assistance, and thanks for the feedback.
"Simon - M&M" wrote:
> That has worked a treat.
>
> Thanks Very Much
>
> Simon
>
> "JLatham" wrote:
>
> > Instead of using Sub Auto_Close() use the workbook's BeforeClose event. It
> > has a specific Cancel parameter which you can set to TRUE to prevent closing.
> >
> > To get to the Workbook's code module, right click on the Excel icon
> > immediately to the left of 'File' in the menu toolbar and choose [View Code]
> > from the popup list. Choose Workbook from the list at the top (starts off
> > with General in it) and then choose BeforeClose from the list to the right of
> > that list.
> >
> > When you select Workbook from the first list, it will put a stub in for
> > SelectionChange; you can delete that stub code at any time. When you choose
> > BeforeClose in the 2nd list, it will provide the stub for that event. You
> > can put your code within that stub. What you want to do is put a line of
> > code in the "NO" response part of your msgbox evaluation like this:
> > Cancel = True
> > (assumes the question is 'Have you saved a copy?') and you want to remain in
> > the workbook if a copy hasn't been saved.
> >
> > When Cancel is set to True, the pending action (Close in this case) is
> > cancelled.
> >
> > "Simon - M&M" wrote:
> >
> > > Hi,
> > >
> > > I'm trying to create a Yes No button that activates when the user tries to
> > > close excel, what i want is that if the user selects yes for the workbook to
> > > save and close. If they click No I want them to be able to go back to the
> > > spreadsheet and carry on working. At the moment all i have got is the yes
> > > bit, when they click no the "Do you want to save changes" box appears and i
> > > can't for the life of me get the macro to select cancel.
> > > Here is the code i'm working on at the minute.
> > > Sub Auto_Close()
> > > '
> > > ' Auto_Close Macro
> > > ' Macro recorded 09/11/2007 by sparrett
> > > '
> > >
> > > Application.DisplayAlerts = False
> > > Dim Msg, Style, Title, Help, Ctxt, Response, MyString
> > > Msg = "Have You Saved A copy?"
> > > Style = vbYesNo + vbInformation
> > > Title = "Clear Down Button"
> > > Help = "DEMO.HLP" ' Define Help file.
> > > Ctxt = 1000 ' Define topic
> > > ' context.
> > > ' Display message.
> > > Response = MsgBox(Msg, Style, Title, Help, Ctxt)
> > > If Response = vbYes Then ' User chose Yes.
> > > MyString = "Yes" ' Perform some action
> > >
> > >
> > > Else ' User chose No.
> > > MyString = "No" ' Perform some action.
> > >
> > > Application.DisplayAlerts = False
> > > ThisWorkbook.Saved = False
> > >
> > >
> > > End If
> > > End Sub
> > >
> > > Any help would be appreciated.
> > >
> > > Cheers
> > >
> > > Simon
|
Similar Threads
