Workbook before close

[Costas]

Hi Chandoo,

I've written some code for the before close event so that I unprotect my workbook, hide all sheets but a blank one and password protect my workbook again. All this is working fine but I keep getting asked if I want to save changes after I run all the code which means that the user will be able to see the changes I've done. Is it possible to avoid getting asked to save?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If MsgBox("Are you sure that you want to close this workbook ?", 36, "Confirm") = vbNo Then
Cancel = True
Else:
ActiveWorkbook.Save
Cancel = False
Application.ScreenUpdating = False
UnprotectWorkbook
Sheets("Sheet1").Visible = True
For Each MySheet In MyBook.Worksheets
If MySheet.Name = "Sheet1" Then
MySheet.Visible = True
Else
MySheet.Visible = False
End If
Next MySheet
ProtectWorkbook
End If

End Sub

Thanks
Costas

 

[Nebu]

Hi:

Is this what your are looking for

ActiveWorkbook.Close SaveChanges:=True

Thanks

 

[Costas]

Hi Nebu,

I tried that but it doesn't work because it restarts the before close event and I end up in a loop.

 

[NARAYANK991]

Hi Costas ,

Have you tried using :

Application.DisplayAlerts = False

Narayan

 

[NARAYANK991]

Hi Costas ,

To avoid getting the message , you can put the .Saved property to True.

Narayan

 

[Costas]

Hi Narayan,

The .Saved property did the trick!

Many thanks
Costas

 

[Ram Mher]

as i close the workbook my all sheets should be protected with password. please help and provide the coding.

 

[Deepak]

as i close the workbook my all sheets should be protected with password. please help and provide the coding.

Hi Ram,

Pls always start a new thread for very own question.

Check this..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet, myP As String

myP = "passTolock"

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    ws.Protect myP
Next

ThisWorkbook.Close True

Application.ScreenUpdating = True

End Sub

 

[Ram Mher]

Hi Ram,

Pls always start a new thread for very own question.

Check this..

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet, myP As String
 
myP = "passTolock"
 
Application.ScreenUpdating = False
 
For Each ws In ThisWorkbook.Worksheets
    ws.Protect myP
Next
 
ThisWorkbook.Close True
 
Application.ScreenUpdating = True
 
End Sub

thanks friend for the solution. workseets are not being protected when i close the file. please provide solution

 

[Deepak]

thanks friend for the solution. workseets are not being protected when i close the file. please provide solution

Have you pasted it into thisworkbook module.

 

[Ram Mher]

Have you pasted it into thisworkbook module.

pasted and saved. lastly closed the workbook and opened again and found that sheets are unprotected

 

[Deepak]

pasted and saved. lastly closed the workbook and opened again and found that sheets are unprotected

Check this..

Unprotect each sheet manually by password passTolock & close the wb.

 

[Ram Mher]

Check this..

Unprotect each sheet manually by password passTolock & close the wb.

friend i unlocked sheet 1 and sheet 2 and then close the workbook. opened again and found sheet 1 and sheet 2 as unprotected ..

 

[Deepak]

friend i unlocked sheet 1 and sheet 2 and then close the workbook. opened again and found sheet 1 and sheet 2 as unprotected ..

Will u pls upload that workbook here.