Application.EnableEvents or Application.CutCopyMode issue?

[g28days]

Hi Everyone,
I need your assistance with an issue I'm having with the following lines in my "ThisWorkbook" code.

I introduced the following codes into Private Sub Workbook_Activate()
Private Sub Workbook_Activate()
Application.EnableEvents = False
Application.CutCopyMode = False
..
....
.....
I have created a sheet to calculate some basic math calculations.
What i'm trying to achieve is to copy range of cells from another normal worksheet (not macro-enabled) and paste into a "Source" worksheet in my match calculation macro-enabled sheet.

Im trying to make it "look" like a standalone app - for my team to use.
So under Private Sub Workbook_Activate(), i have included some events to hide FormulaBar, Displaybar, etc.

The entire code for "ThisWorkbook" is listed below.

Private Sub Workbook_Open()
Call DefZoom
Call DefOpenSheet
Call DisableCommand_Enable
Call AssignSNtoSheet


Sub DefZoom()
'Set Default Zoom
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim currentWS As Worksheet: Set currentWS = ActiveSheet
For Each ws In ActiveWorkbook.Worksheets
   If ws.Visible = xlSheetVisible Then
      ws.Activate
      ActiveWindow.Zoom = 80 ' <<--- Change zoom size here
   End If
Next ws
currentWS.Select
End Sub


Sub DefOpenSheet()
'Set Default Worksheet
Sheets("EULA (T&C)").Activate  ' <<--- Change range here, if you want
End Sub




Sub DisableCommand_Enable()
' Protect the sheet from being deleted and renamed
' Manually run DisableCommand_Disable to remove this when the file is open
With Application
With .CommandBars("Worksheet Menu Bar")
 .Controls("Edit").Controls("Delete Sheet").Enabled = False
 .Controls("Format").Controls("Sheet").Controls("Rename").Enabled = False
End With
With .CommandBars("Ply")
 .Controls("Delete").Enabled = False
 .Controls("Rename").Enabled = False
End With
End With
End Sub


Sub DisableCommand_Disable()
 ' Unprotect the sheet from being deleted and renamed
With Application
With .CommandBars("Worksheet Menu Bar")
 .Controls("Edit").Controls("Delete Sheet").Enabled = True
 .Controls("Format").Controls("Sheet").Controls("Rename").Enabled = True
End With
With .CommandBars("Ply")
 .Controls("Delete").Enabled = True
 .Controls("Rename").Enabled = True
End With
End With
End Sub


Private Sub Workbook_Activate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.CutCopyMode = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = True
    Application.ScreenUpdating = True
    'Application.OnKey "{ESC}", ""  ' to disable
    'Application.OnKey "^c", "" 'Copy shortcut disabled
    'Application.OnKey "^v", "" 'Paste shortcut disabled
    'Application.OnKey "^x", "" 'Cut shortcut disabled
    'Application.OnKey "^p", "" 'Print shortcut disabled
    'Application.OnKey "^s", "" 'Save shortcut disabled
    'Application.OnKey "%f", "" 'F shortcut disabled
    'Application.OnKey "{F2}", "" 'F2 shortcut disabled
End Sub


Private Sub Workbook_Deactivate()
    Application.ScreenUpdating = False
    Application.EnableEvents = True
    Application.CutCopyMode = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    'Application.OnKey "{ESC}"      ' to reenable
    'Application.OnKey "^c" 'Copy shortcut enabled
    'Application.OnKey "^v" 'Paste shortcut enabled
    'Application.OnKey "^x" 'Cut shortcut enabled
    'Application.OnKey "^p" 'Print shortcut enabled
    'Application.OnKey "^s" 'Save shortcut enabled
    'Application.OnKey "%f", "" 'F shortcut enabled
    'Application.OnKey "{F2}", "" 'F2 shortcut enabled
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI = False Then
Cancel = True
MsgBox "The 'Save'  function for this workbook has      " & Chr(10) & "been disabled.  Please Use The 'Save As' Button.", vbOKOnly + vbInformation, "Save Disabled"
End If
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '// Do not use the In-line If...Then statement here
    If Not CloseMode Then
        Cancel = True
        MsgBox "Please Use Quit To Close This File"
    End If
End Sub

Now the above code works well when i need to copy and paste data from another workbook to my macro workbook here.

However, i realized 2 things.
1. This following code does not work:- Its supposed to prevent the "X" and the top right hand corner of the workbook, since I introduced the codes "Application.EnableEvents = True & Application.CutCopyMode = True" into ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '// Do not use the In-line If...Then statement here
    If Not CloseMode Then
        Cancel = True
        MsgBox "Please Use Quit To Close This File"
    End If
End Sub

2. I have a sheet named "Template": In it i have the following code. This code has also stopped working since I introduced the codes "Application.EnableEvents = True & Application.CutCopyMode = True" into thisWorkbook

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BC As Range, t As Range, v As Variant
    Dim r As Long
    Set t = Target
    Set BC = Range("D11:G11")
    If Intersect(t, BC) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r = t.Row
        v = t.Value
        If v = "" Then
            Range("D11" & r & ":G11" & r).Value = ""
        End If
        If IsNumeric(v) Then
            If Intersect(t, Range("G11:G11")) Is Nothing Then
                t.Offset(0, 3).Value = v / 3.28
            Else
                t.Offset(0, -3).Value = v * 3.28
            End If
        End If
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Name <> "Template" Then
        ActiveSheet.Name = "Template"
    End If
End Sub

I'm a huge fan of the updates and help in this forum. I've learnt great many things from your answers to other individuals.
I would truly appreciate if you can please help me with my query and point me in the right direction.

Regards.,

 

[RoryA]

I’m afraid your approach is flawed. You can’t use an event to re-enable events, since it won’t run when events are disabled. As soon as your workbook_activate code runs, none of the other event codes will function.

 

[g28days]

I’m afraid your approach is flawed. You can’t use an event to re-enable events, since it won’t run when events are disabled. As soon as your workbook_activate code runs, none of the other event codes will function.

Hi RoryA,
Thank you for your feedback. Is there anyway i can incorporate the "Application.CutCopyMode"; I only need to use it in one of the worksheets called "source"; apart from that i don't require it anywhere else.
I tried the following in the "source" worksheet

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
        Application.EnableEvents = False
    	Application.CutCopyMode = True
	Application.EnableEvents = True  
End Sub

And removed the lines "ThisWorkbook"
Am I doing anything incorrect? Again, I appreciate any help that I can get.
Regards.,

 

[g28days]

Hi RoryA,
Thank you for your feedback. Is there anyway i can incorporate the "Application.CutCopyMode"; I only need to use it in one of the worksheets called "source"; apart from that i don't require it anywhere else.
I tried the following in the "source" worksheet

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
        Application.EnableEvents = False
        Application.CutCopyMode = False
    Application.EnableEvents = True  
End Sub

And removed the lines "ThisWorkbook"
Am I doing anything incorrect? Again, I appreciate any help that I can get.
Regards.,

Amended code

 

[RoryA]

Your problem is here:

Private Sub Workbook_Activate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False

If you have the red line, none of the other code will be able to fire unless you restart Excel or manually run code to reset events. Why do you need to disable events there?

 

[g28days]

Your problem is here:

Private Sub Workbook_Activate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False

If you have the red line, none of the other code will be able to fire unless you restart Excel or manually run code to reset events. Why do you need to disable events there?

Hi RoryA,
Yes, you are right, but without it, the next line "Application.CutCopyMode = False" does not run. And Im not able to copypaste data from another worksheet.

 

[RoryA]

That doesn't make any sense. Setting CutCopyMode to False would stop you from copying from another workbook, and disabling events before that would not affect that line.