I have a excel file that I'm trying to prevent someone from saving as or
coping data. I have disabled the "Save", "Save as..", and the copy commands.
But I notice when someone select the entire sheet (Ctrl - A) the copy command
is still available on the right click menu. How do I disable this?

Actually there are many instances of the Copy command (32) sprinkled among
the commandbars. To get them all:

Sub DisableCopyCtrl()
Dim Ctrls As CommandBarControls
Dim Ctrl As CommandBarControl
Set Ctrls = CommandBars.FindControls(, 19)
For Each Ctrl In Ctrls
Ctrl.Enabled = False
Next
End Sub

Be sure to Enable on exit as this change is saved in the user's
configuration.

But you know if the user disables macros when opening your workbook he'll be
able to copy as he pleases...

--
Jim
"Firkins" wrote in message
...
|I have a excel file that I'm trying to prevent someone from saving as or
| coping data. I have disabled the "Save", "Save as..", and the copy
commands.
| But I notice when someone select the entire sheet (Ctrl - A) the copy
command
| is still available on the right click menu. How do I disable this?

Thanks Jim that works great.
I have my sheets very hidden with code that unhides them on open. If macros
are not enable they can't see anything.

"Jim Rech" wrote:

Actually there are many instances of the Copy command (32) sprinkled among
the commandbars. To get them all:

Sub DisableCopyCtrl()
Dim Ctrls As CommandBarControls
Dim Ctrl As CommandBarControl
Set Ctrls = CommandBars.FindControls(, 19)
For Each Ctrl In Ctrls
Ctrl.Enabled = False
Next
End Sub

Be sure to Enable on exit as this change is saved in the user's
configuration.

But you know if the user disables macros when opening your workbook he'll be
able to copy as he pleases...

--
Jim
"Firkins" wrote in message
...
|I have a excel file that I'm trying to prevent someone from saving as or
| coping data. I have disabled the "Save", "Save as..", and the copy
commands.
| But I notice when someone select the entire sheet (Ctrl - A) the copy
command
| is still available on the right click menu. How do I disable this?

Hi Firkins

If your code also must run on Office 97 see this page for another loop
http://www.rondebruin.nl/menuid.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Firkins" wrote in message ...
Thanks Jim that works great.
I have my sheets very hidden with code that unhides them on open. If macros
are not enable they can't see anything.

"Jim Rech" wrote:

Actually there are many instances of the Copy command (32) sprinkled among
the commandbars. To get them all:

Sub DisableCopyCtrl()
Dim Ctrls As CommandBarControls
Dim Ctrl As CommandBarControl
Set Ctrls = CommandBars.FindControls(, 19)
For Each Ctrl In Ctrls
Ctrl.Enabled = False
Next
End Sub

Be sure to Enable on exit as this change is saved in the user's
configuration.

But you know if the user disables macros when opening your workbook he'll be
able to copy as he pleases...

--
Jim
"Firkins" wrote in message
...
|I have a excel file that I'm trying to prevent someone from saving as or
| coping data. I have disabled the "Save", "Save as..", and the copy
commands.
| But I notice when someone select the entire sheet (Ctrl - A) the copy
command
| is still available on the right click menu. How do I disable this?

Hi

I am very new to VB and have tried Jim's code below to disable the copy
command in user templates I am creating however it didn't work.

I went into VB (Alt + F11) and pasted the code into the ThisWorkbook module.
Is that correct??

Also I didn't know what Jim meant about "ensure to enable on exit".

Can someone help please??

Thanks and Merry Christmas. Silena


"Jim Rech" wrote:

Actually there are many instances of the Copy command (32) sprinkled among
the commandbars. To get them all:

Sub DisableCopyCtrl()
Dim Ctrls As CommandBarControls
Dim Ctrl As CommandBarControl
Set Ctrls = CommandBars.FindControls(, 19)
For Each Ctrl In Ctrls
Ctrl.Enabled = False
Next
End Sub

Be sure to Enable on exit as this change is saved in the user's
configuration.

But you know if the user disables macros when opening your workbook he'll be
able to copy as he pleases...

--
Jim
"Firkins" wrote in message
...
|I have a excel file that I'm trying to prevent someone from saving as or
| coping data. I have disabled the "Save", "Save as..", and the copy
commands.
| But I notice when someone select the entire sheet (Ctrl - A) the copy
command
| is still available on the right click menu. How do I disable this?

Hi Silena

Copy both in a normal module
http://www.rondebruin.nl/code.htm

Sub MenuControl_False()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=19)
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub MenuControl_True()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=19)
Ctrl.Enabled = True
Next Ctrl
End Sub

Then in the Thisworkbook module use

Private Sub Workbook_Activate()
Call MenuControl_False
End Sub

Private Sub Workbook_Deactivate()
Call MenuControl_True
End Sub






Read also the info here
http://www.rondebruin.nl/menuid.htm

..

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Silena K-K" wrote in message ...
Hi

I am very new to VB and have tried Jim's code below to disable the copy
command in user templates I am creating however it didn't work.

I went into VB (Alt + F11) and pasted the code into the ThisWorkbook module.
Is that correct??

Also I didn't know what Jim meant about "ensure to enable on exit".

Can someone help please??

Thanks and Merry Christmas. Silena


"Jim Rech" wrote:

Actually there are many instances of the Copy command (32) sprinkled among
the commandbars. To get them all:

Sub DisableCopyCtrl()
Dim Ctrls As CommandBarControls
Dim Ctrl As CommandBarControl
Set Ctrls = CommandBars.FindControls(, 19)
For Each Ctrl In Ctrls
Ctrl.Enabled = False
Next
End Sub

Be sure to Enable on exit as this change is saved in the user's
configuration.

But you know if the user disables macros when opening your workbook he'll be
able to copy as he pleases...

--
Jim
"Firkins" wrote in message
...
|I have a excel file that I'm trying to prevent someone from saving as or
| coping data. I have disabled the "Save", "Save as..", and the copy
commands.
| But I notice when someone select the entire sheet (Ctrl - A) the copy
command
| is still available on the right click menu. How do I disable this?