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."

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."

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