Protect Cells

[juanpablo]

I have the option of cells protected and the sheet with password.
But, how do I protect unprotected cells of users from using the option
copy-cut-paste?

Thanks!!

JPG

 

[Eduardo]

Hi,
when you protect the sheet, you tell excel what you will allow the users to
do if you don't check anything they will not able to cut or paste just read

 

[Gord Dibben]

You cannot stop users from acting upon unlocked cells in a protected sheet.

That is.........without the use of VBA code.


Gord Dibben MS Excel MVP

 

[juanpablo]

Yes but in all the options it does not appear one that specifies cut.copy and
paste.

JPG

 

[Eduardo]

Hi Juanpablo,
If you unprotect the cells when protecting the sheet they will be able to
copy and paste

 

[juanpablo]

Yes, I found VBA code that works fine, the problem now is that the book is
shared.

JP

 

[Gord Dibben]

Too bad.

One of the many problems with shared workbooks.

Post the code.............there may be a way but if it involves unprotect
then reprotect you are out of luck.


Gord

 

[Juan Pablo Gallardo]

Im using this one:

Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this
workbook!"
End Sub

and called on the workbook:

Option Explicit

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub

 

[Gord Dibben]

What exactly does it not do that makes it a problem for you?

In Excel 2003 your code disables cut/copy etc. when opened or activated.

Reenables when workbook is de-activated or closed.

With workbook shared and sheet protection enabled on multiple sheets.


Gord

 

[Juan Pablo Gallardo]

With shared workbooks, the code does not work, its kind of deleted from the
excel file.

JP

 

[Gord Dibben]

In shared workbooks, the code is not deleted, just hidden and non-accessible
for editing.

Your Thisworkbook code is event code and runs the macros in the General
module so you need no access.

Runs just fine for me in Excel 2003.


Gord