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

Cancel user's changes but save other changes
 

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)

wrote in message
oups.com...
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