Solved: Clear Contents on Workbook Close

[gimli]

Hey all,

Looking to put some code in to clear the contents of an entire worksheet when the workbook is closed.

EX.


Sheet1.Cells.ClearContents



That should clear the contents..but what extra code is needed to do it on closing the workbook only.

thanks much

[Pinokkio]

Alt+F11
find ThisWorkbook



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Cells.ClearContents
End Sub

[lucas]

You will probably wish to save it before it closes too:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Cells.ClearContents
ActiveWorkbook.Save
End Sub

[gimli]

Ok..heres whats in my workbook page.

1. Locking some cells
2. Preventing anybody but the author saving changes.
3. Trying to add script so when the workbook is closed it clears contents on sheet 2.

Im thinking a problem will occur if the user is not "joe blow" it wont save the workbook after it clears the contents?

any sugestions? Thanks much



Private Sub Workbook_Open()
Sheet1.ScrollArea = "b1:q27"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
If Environ("username") = "Joe Blow" Then
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
Else
MsgBox "This work book cannot be saved"
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet2.Cells.ClearContents
ThisWorkbook.Save
End Sub

[mdmackillop]

If the user cannot save changes, is there any need to clear contents?

[gimli]

Hrmm good point :think: .

Your right...why bother.