Protect Cells
 

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

Protect Cells
 

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

"juanpablo" wrote:

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

Protect Cells
 

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

Protect Cells
 

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

JPG

"Eduardo" wrote:

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

"juanpablo" wrote:

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

Protect Cells
 

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

"juanpablo" wrote:

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

JPG

"Eduardo" wrote:

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

"juanpablo" wrote:

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

Protect Cells
 

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

JP

"Gord Dibben" wrote:

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

.

Protect Cells
 

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

On Tue, 27 Apr 2010 11:38:01 -0700, juanpablo
wrote:

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

JP

"Gord Dibben" wrote:

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

.

Protect Cells
 

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" <gorddibbATshawDOTca escribió en el mensaje
...
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

On Tue, 27 Apr 2010 11:38:01 -0700, juanpablo
wrote:

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

JP

"Gord Dibben" wrote:

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

.

Protect Cells
 

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

On Wed, 28 Apr 2010 10:49:04 -0500, "Juan Pablo Gallardo"
wrote:

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" <gorddibbATshawDOTca escribió en el mensaje
.. .
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

On Tue, 27 Apr 2010 11:38:01 -0700, juanpablo
wrote:

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

JP

"Gord Dibben" wrote:

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

.

Protect Cells
 

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

JP
"Gord Dibben" <gorddibbATshawDOTca escribió en el mensaje
...
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

On Wed, 28 Apr 2010 10:49:04 -0500, "Juan Pablo Gallardo"
wrote:

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" <gorddibbATshawDOTca escribió en el mensaje
. ..
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

On Tue, 27 Apr 2010 11:38:01 -0700, juanpablo
wrote:

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

JP

"Gord Dibben" wrote:

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

.

Protect Cells
 

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

On Thu, 29 Apr 2010 12:26:03 -0500, "Juan Pablo Gallardo"
wrote:

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

JP
"Gord Dibben" <gorddibbATshawDOTca escribió en el mensaje
.. .
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

On Wed, 28 Apr 2010 10:49:04 -0500, "Juan Pablo Gallardo"
wrote:

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" <gorddibbATshawDOTca escribió en el mensaje
...
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

On Tue, 27 Apr 2010 11:38:01 -0700, juanpablo
wrote:

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

JP

"Gord Dibben" wrote:

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

On Tue, 27 Apr 2010 07:56:02 -0700, juanpablo
wrote:

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

.