Disable Cut and paste
 

Hi all,

I found a script in an old EEE newsletter on j-walk.com that disables cut and paste in a workbook/sheet. This is something that I need desperately as my staff insist on using cut and paste in their timesheets and it is driving me bereft. If I had a donut for every time I'd told them not to do it I'd need Greenpeace to protect me.

Basically it works and does what I require except that on Workbook_Open I get a debug msg box. If I select "run" the macro works. This is a bit annoying. On "debug" the editor goes to:

Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next
End Sub

The more I fiddle with it the more my ignorance shows. Any help will be appreciated.

the script is at http://www.j-walk.com/ss/excel/eee/eee020.txt


Regards

Roy Collins.

"You don't need to be good. You just need to be able to convince people you are."

Disable Cut and paste
 

Hi Roy,

Try

'=============
Public Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As Office.CommandBar
Dim C As CommandBarButton
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not CB.Name = "Clipboard" Then
If Not C Is Nothing Then C.Enabled = Enabled
End If
Next
End Sub
'<<=============

---
Regards,
Norman


"Roy" wrote in message
...
Hi all,

I found a script in an old EEE newsletter on j-walk.com that disables cut
and paste in a workbook/sheet. This is something that I need desperately as
my staff insist on using cut and paste in their timesheets and it is driving
me bereft. If I had a donut for every time I'd told them not to do it I'd
need Greenpeace to protect me.

Basically it works and does what I require except that on Workbook_Open I
get a debug msg box. If I select "run" the macro works. This is a bit
annoying. On "debug" the editor goes to:

Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next
End Sub

The more I fiddle with it the more my ignorance shows. Any help will be
appreciated.

the script is at http://www.j-walk.com/ss/excel/eee/eee020.txt


Regards

Roy Collins.

"You don't need to be good. You just need to be able to convince people you
are."

Disable Cut and paste
 

Hi Roy,

The intended adaptation was:

'=============
Public Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not CB.Name = "Clipboard" Then
If Not C Is Nothing Then C.Enabled = Enabled
End If
Next
End Sub
'<<=============

---
Regards,
Norman