Enabling Cut,Copy,Paste,Paste-Special Functions

[gmcgrane]

Hi All,
I used a similar Macro some time ago to disable to cut/copy paste feature. This has also prevented me from the right click menu altogether.
I need to renable this can you help please





Private Sub Workbook_Activate() Dim oCtrl As Office.CommandBarControl 'Disable all Cut menus For Each oCtrl In Application.CommandBars.FindControls(ID:=21) oCtrl.Enabled = False Next oCtrl 'Disable all Copy menus For Each oCtrl In Application.CommandBars.FindControls(ID:=19) oCtrl.Enabled = False Next oCtrl Application.CellDragAndDrop = False End Sub Private Sub Workbook_Deactivate() Dim oCtrl As Office.CommandBarControl 'Enable all Cut menus For Each oCtrl In Application.CommandBars.FindControls(ID:=21) oCtrl.Enabled = True Next oCtrl 'Enable all Copy menus For Each oCtrl In Application.CommandBars.FindControls(ID:=19) oCtrl.Enabled = True Next oCtrl Application.CellDragAndDrop = True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) With Application .CellDragAndDrop = False .CutCopyMode = False 'Clear clipboard End With End Sub Note the use of the FindControls Method, In particular the use of the optional ID argument. One can determine the ID of any Control via some simple code Like shown below. Sub Copy_Id() MsgBox CommandBars("Worksheet Menu Bar") _ .Controls("Edit").Controls("Copy").ID End Sub </pre>

 

[schielrn]

Can you not just run the routine that enables everything and take out the workbook_activate code?

Sub enableCommands()
Dim oCtrl As Office.CommandBarContro
 'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl
Application.CellDragAndDrop = True
End Sub

 

[gmcgrane]

Can you not just run the routine that enables everything and take out the workbook_activate code?

Sub enableCommands()
Dim oCtrl As Office.CommandBarContro
 'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl
Application.CellDragAndDrop = True
End Sub

Hi schielrn,
Thanks For your help really appreciate it. I have tried this but it still will not work.
I get an Error message:
Compile Error:
User-defined type not defined

 

[schielrn]

I am missing a letter L at the end of the 2nd line, not sure if you fixed that or not?

 

[gmcgrane]

I am missing a letter L at the end of the 2nd line, not sure if you fixed that or not?

Agh Brilliant that worked like a charm
Thank You