Excel 2013 protect issue

[KetanGajare]

Hi I have written a simple code. Which copies Data from one sheet and paste to another sheet.
Running code from sheet1. But once the macro runs completeley the activated sheet is Sheet3. Ideally it should be sheet1. I am using Excel 2013.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call test
End Sub

Public Sub test()
Application.EnableEvents = False
EstSheetStart = 33
EstSheetEnd = 1400
With Sheets("sheet3")
.Unprotect Password:="abc"
.Range("AN" & EstSheetStart & ":BK" & EstSheetEnd).ClearContents
Sheets("sheet2").Range("A1:AQ500").Copy
.Range("A" & EstSheetStart).PasteSpecial Paste:=xlValues
.Protect Password:="abc"
End With
Application.EnableEvents = True
End Sub


Thanks for help in advance

 

[dourpil]

Wouldn't adding a line such as "Sheet1.Activate" right before "End sub" do the trick?

 

[KetanGajare]

Thanks dourpil,
I wanted to know why this wired behaviour in Excel 2013. In Excel 2007 and Excel 2010 the code runs properly and i need not required to activate the sheet1.

Is this happening because of new algorithm used for Protect and Unprotect in Excel 2013.

Thanks

 

[Abdul Jamal]

Hi, I'm have found a code to protect my file as below:

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Select Case True
Case SaveAsUI
MsgBox "Sorry, you may not ''Save as'' this workbook." & vbCrLf & vbCrLf & "You may only save it, keeping its original name.", 48, "''Save as'' not allowed."
Cancel = True
Case Else
Exit Sub
End Select
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Confidential: Not Printable File"
Cancel = True
End Sub

It was protected from print, copy and paste and save as function.
But one more copy function was not protect, it is "copy as picture"

does somebody know how to enable this function?
Thanks in advance..