Worksheet_Deactivate() problem with deleting data

[Karoo News]

Hi I need to delete formaulas when a sheet deactivates but the formulas in
the next sheet thats acivated are deleted instead, is there a way to do
this? (the formaulas are copied in when the worksheets are activated)

Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect Password:="naWages"
Range("AG17:AH41").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("AY17:CI42").Select
Selection.ClearContents
Range("A1").Select
ActiveSheet.Protect Password:="naWages"
End Sub

Regards
Neil

 

[Norman Jones]

Hi Neil,

The following adaptation worked for me:

'====================>>
Private Sub Worksheet_Deactivate()

On Error GoTo XIT

Application.EnableEvents = False

With Me

.Unprotect Password:="naWages"

With .Range("AG17:AH41")
.Value = .Value
End With

.Range("AY17:CI42").ClearContents
.Protect Password:="naWages"

End With

XIT:
Application.EnableEvents = True

End Sub
'<<====================

BTW. apart from the fact that selections are rarely necessary, or desirable,
I would have expected your code to fail on the line:

Range("AG17:AH41").Select

given that a selection cannot be made on an inactive sheet.

 

[Tom Ogilvy]

why not use the workbook level sheetdeactivate event. then you have a
reference to the sheet being deactivated. Remove all the selecting - since
the sheet is not the active sheet. write references to your locations

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
if sh.Name = "MySheet" then
with sh
.Unprotect Password:="naWages"
. Range("AG17:AH41").Formula = _
.Range("AG17:AH41.Value
.Range("AY17:CI42").ClearContents
.Protect Password:="naWages"
End With
End if
End Sub

 

[Karoo News]

That worked fantastic - many thanks - Neil

Hi Neil,

The following adaptation worked for me:

'====================>>
Private Sub Worksheet_Deactivate()

On Error GoTo XIT

Application.EnableEvents = False

With Me

.Unprotect Password:="naWages"

With .Range("AG17:AH41")
.Value = .Value
End With

.Range("AY17:CI42").ClearContents
.Protect Password:="naWages"

End With

XIT:
Application.EnableEvents = True

End Sub
'<<====================

BTW. apart from the fact that selections are rarely necessary, or
desirable, I would have expected your code to fail on the line:


given that a selection cannot be made on an inactive sheet.