save on Sheet Close

[pandey_om]

Hi Experts,
On closing an excel sheet the excel provoid the mgs;
do u want to save the change in file.xls
YES NO CANCLE
i wana the write some code on YES click
PLZ HELP

[cssriraman]

what code you want to write? what is your need?

Please explain little more. so that we can understand better to help you.

[pandey_om]

i want to save the cells value to the database and sheet On YES click

[VBAhack]

I believe what you are looking for is the BeforeSave workbook event. Place this on your workbook code sheet and modify the stuff in the middle:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim iReply As Integer
   If SaveAsUI = True Then
      iReply = MsgBox("Sorry, you are not allowed to save this Workbook as another name. " _
                     & "Do you wish to save this workbook.", vbQuestion + vbOKCancel)
     Cancel = (iReply = vbCancel)
   If Cancel = False Then Me.Save
     Cancel = True
  End If
End Sub

[pandey_om]

sorry i am not looking for it
i want the code on ,when MS-Excel give the Message on sheet/book close ,on YES

[si_the_geek]

I haven't tested, but I think the only code you need in the Sub from VBAhack's example is this:

Me.Save

..however, you will probably want to put it into the BeforeClose event instead of BeforeSave.

[RobDog888]

ThisWorkbook.Save

Works too but VBAHacks code is making sure its not "Save As" as to retain the original workbook name.

[pandey_om]

sorry i am not looking for it
i want the code on ,when MS-Excel give the Message box on sheet/book close
DO you want to save the changes made in .xls file
YES NO Cancle
i wana code on only cliking 'YES'

[RobDog888]

See post #4.

[cssriraman]

sorry i am not looking for it
i want the code on ,when MS-Excel give the Message box on sheet/book close
DO you want to save the changes made in .xls file
YES NO Cancle
i wana code on only cliking 'YES'

We are not sure what you are looking for?

Do you want to click "Yes" buton?

or

Do you want to save some values to database if the user clicks the "Yes" button?

[pandey_om]

i want to save some values to database if the user clicks the "Yes" button?

[pandey_om]

the steps are
1)open work sheet
2)write data on sheet
3)on closing sheet
MS-Excel Messg "DO you want to save chang...."
YES , NO, Cancel
if i click YES the data must be save, not on click NO and Cancle

[RobDog888]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Cancel = False And SaveAsUI = False Then
        Me.Save
        Cancel = True
        'Add code to save data to database here:
        '
    End If
End Sub

[pandey_om]

i think it will work for me
thankx u

[pandey_om]

on clicking "YES"
1) Data are saved to database(Working Fine)
But 2) worksheet not close and MS-Excel Msg remainnig

[pandey_om]

3) worksheet not saved
its working fine for Excel closed but not for sheet closed

[RobDog888]

How could Excel close but not a sheet?

Take the "Cancel = True" out.

[pandey_om]

thanks for your support
all working fine but one problem is that when i click YES ,in database records saved two times.

[pandey_om]

i think problem is due to ME.Save or any