Hey,
Found a macro for disabling copy/paste on this page:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=373
..but I want to be able to switch it back on again by changing a value in a cell. The macro for copy/pasting is in both ThisWorkbook and in a module.
The macro that I'm looking for is probably easiest seen from my trials (=errors) shown below.
Thanks a lot for your help.
'*** In the ThisWorkbook Module ***
Option Explicit
Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub
'Private Sub Workbook_Open()
' Call ToggleCutCopyAndPaste(False)
'End Sub
'code is working fine with the three lines above activated, but I then tried adding following lines:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Sheet1").Range("H6").Value = "Enable" Then
Module1.ToggleCutCopyAndPaste.Enable = False
Else
Exit Sub
End If
End Sub
or
Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As String
If Target.Column = 8 And Target.Row = 6 Then
val = Target.Value
If val = "Enable" Then
Module1.ToggleCutCopyAndPaste = False
Else
Exit Sub
End If
End Sub
Bookmarks