Disable Copy & Formula Toolbar

[Giri]

Hi,

1. How can we disable the copy option in a workbook?
We have a protected workbook such that for the end user
some cells are protected and some cells editable. To
increase protection we want to disable the copy option.
The solution could be from the vb code but in that case i
would also want to know how to disable a user from going
to the vb editor or seeing the macros. Further, plz also
tell how to disable the copy option at worksheet level &
workbook level
2. On the same lines as above, i also want to permanently
disable in this workbook, the formula bar. No user should
be able to enable it.

Note: We are using Office2000 & Windows 2000 Pro.

Any help in this regard is appreciated. Thank You.

Regards,
Giri

 

[DNF Karran]

Giri,

I was about to have a look at your problem but I have to ask is ther
any point disabling the function button on the toolbar? If users canno
edit protected cells why does it need to be disabled? They can ge
round this by just manually adding a formula by putting "=" at th
start.

To disable the toolbars you can use VBA to disable them on open an
enable them on close. The "ctrl+c" can also be disabled. Agai
however, is this a worthwhile aim? The data can be copied from
spreadsheet by opening another workbook, putting "=[Book1]Sheet1!A1
and dragging right and down. A copy of the data will then be availabl
(less any fomatting). A recorded/written macro could also be run tha
copies the selected range and again, the disabling of the toolbar an
keyboard shortcut will be bypassed.

If you want to proceed despite these flaws then reply to this post an
I can at least point you in the right direction to go in.

Dunca

 

[Giri]

Hello Duncan,

Yes, i understand what you are saying. And yes, i may
not do what i proposed to do since it may not be of much
use as you have shown. But, just for the sake of knowing
how it can be done, i would still like to know how to go
abt it. Really appreciate if you can help me to that end.

Regards,
Giri

 

[DNF Karran]

Ok, I'll sit down and work out the details in about an hour or so as
have some proper work to do first!

Dunca

 

[DNF Karran]

Sorry for the delay!! Month end etc....

If you put this in the Workbook_open section of the workbook object

'Step 1- Disable Ctrl+C
Application.OnKey "^c"

'Step 2- Disable the menus
CommandBars("Worksheet Menu Bar").Controls("Edit").Enabled = False
CommandBars("Standard").Controls("Paste Function").Enabled = False

and this in the Workbook_close object

'Step 1- Enable Ctrl+C
Application.OnKey "^c", NewCopy

'Step 2- Enable the menus
CommandBars("Worksheet Menu Bar").Controls("Edit").Enabled = True
CommandBars("Standard").Controls("Paste Function").Enabled = True

And make a new sub to reinstate copy

sub NewCopy
selection.copy
end sub

For more workbar examples see command bar blackjack by Rob van Gelde
(attached). As it's all command bar based will show you lots of thing
you can do with the command bars.

Dunca

Attachment filename: blackjack.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=52994