Cancel user's changes but save other changes

Hi,

I've modified the workbook before close function as follows...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not Me.Saved Then
RetVal = MsgBox("Do you want to save the changes you made to '"
& Me.Name & "'?", vbExclamation + vbYesNoCancel)
Select Case RetVal
Case vbYes
Me.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If

// my code with some changes

Me.Save

End Sub

If the user specifies 'No' to changes, then how to I cancel any changes
by the user, but still perform some of my own changes and then save
those, and then close the workbook ??

I want to perform my changes at the very last moment - i.e. without
giving the user an opportunity to cancel closing the workbook after the
code has run.

Basically I'm looking for an alternative to "fooling" excel with the
me.saved=true bit, e.g. me.discardanychanges (or something similar !)

Some assistance would be much appreciated !

Thanks in advance

Jeremy

I think you would have to re-open the workbook, apply your changes, then
close it down. This of course would mean that your code would need to be
elsewhere.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I've modified the workbook before close function as follows...
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> If Not Me.Saved Then
> RetVal = MsgBox("Do you want to save the changes you made to '"
> & Me.Name & "'?", vbExclamation + vbYesNoCancel)
> Select Case RetVal
> Case vbYes
> Me.Save
> Case vbNo
> Me.Saved = True
> Case vbCancel
> Cancel = True
> Exit Sub
> End Select
> End If
>
> // my code with some changes
>
> Me.Save
>
> End Sub
>
> If the user specifies 'No' to changes, then how to I cancel any changes
> by the user, but still perform some of my own changes and then save
> those, and then close the workbook ??
>
> I want to perform my changes at the very last moment - i.e. without
> giving the user an opportunity to cancel closing the workbook after the
> code has run.
>
> Basically I'm looking for an alternative to "fooling" excel with the
> me.saved=true bit, e.g. me.discardanychanges (or something similar !)
>
> Some assistance would be much appreciated !
>
> Thanks in advance
>
> Jeremy
>