Is it possible to make pasting not allowed?

[AA Arens]

Is it possible to make pasting not allowed (the cells are un-locked,
but the sheet is protected)?

Bart
Excel 2003

 

[Bob Phillips]

Lock the cells as well?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[AA Arens]

It is a workbook for my staff. I only want to make it able that they
fill in the cells requirered data and cannot access other cells wiith
formulas etc.
I want them to fill in the data but not by copy/pasting to avoid
inaccuraties.

Bart

 

[Bob Phillips]

Unprotect the sheet.
Select all cells and lock them.
Select the cells that you want thm to be able to change them, and Unlock
them.
Protect the sheet.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[AA Arens]

Bob,

Your described the is the common way, but now also want that
copy/pasting is not possible, only filling dat and choose value vioa
built in combo boxes.

 

[Bob Phillips]

You can't, if you want that sort of capability, you need comboboxes rather
than data validation.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[ilia]

On workbook_open, unmap the Ctrl+V and the Shift+Ins key combinations.
Disable the menu option. This way, users won't be able to paste.
Re-enable this functionality on workbook_close.


Application.OnKey "^V", ""
Application.OnKey "^{INSERT}",""

Application.CommandBars("Edit").Controls("Paste").Enabled = False
Application.CommandBars("Edit").Controls("Paste Special...").Enabled =
False

You have to be sure your macro security level is appropriate so that
users aren't presented with the option to disable macros at startup.


"""Bob Phillips ÐÉÓÁÌ(Á):
"""

 

[AA Arens]

It doesn't work. Find below the VB code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Edit").Controls("Paste").Enabled = False
Application.CommandBars("Edit").Controls("Paste
Special...").Enabled = False

End Sub

Private Sub Workbook_Open()
Application.OnKey "^V", ""
Application.OnKey "^{INSERT}", ""
End Sub

I should not do it with Activate and De-activate?

Macro's are accepted.


Bart

 

[ilia]

Sorry, that was rather incomplete. Apologies.

You want to disable the following:
* Cut, Copy, Paste - key combinations, both upper- and lower-case
* SHIFT+DEL, CTRL+DEL, SHIFT+INSERT
* The menu controls, respectively
* The controls on the command bar

You do this on Workbook_Open. You do the reverse on
Workbook_BeforeClose.

Here is the complete code:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.OnKey "^V"
.OnKey "^v"
.OnKey "^c"
.OnKey "^C"
.OnKey "^x"
.OnKey "^X"
.OnKey "^{DEL}"
.OnKey "^{DELETE}"
.OnKey "^{INSERT}"
.OnKey "+{DEL}"
.OnKey "+{DELETE}"
.OnKey "+{INSERT}"
With .CommandBars("Edit")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
.Controls("Paste Special...").Enabled = True
End With
With .CommandBars("Standard")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
End With
End With
End Sub

Private Sub Workbook_Open()
With Application
.OnKey "^V", ""
.OnKey "^v", ""
.OnKey "^c", ""
.OnKey "^C", ""
.OnKey "^x", ""
.OnKey "^X", ""
.OnKey "^{DEL}", ""
.OnKey "^{DELETE}", ""
.OnKey "^{INSERT}", ""
.OnKey "+{DEL}", ""
.OnKey "+{DELETE}", ""
.OnKey "+{INSERT}", ""
With .CommandBars("Edit")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With
With .CommandBars("Standard")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
End With
End With
End Sub



Hope that helps.

 

[ilia]

Oh, and if you expect your users to have other workbooks open alongside
your data entry book, yes Activate and Deactivate will be more
appropriate.

 

[AA Arens]

It works with the code you provided, incl the buttons on the bar. I am
only still able to use right click > copy/paste.

 

[ilia]

I missed that one, in fact just thought of it this morning.

There are three command bars - Cell, Column, and Row - that contain the
right-click menu options. You can disable those, to prevent any kind
of right-click menu showing:

..CommandBars("Cell").Enabled = False
..CommandBars("Column").Enabled = False
..CommandBars("Row").Enabled = False

.... or you can do the same as we did with the Edit menu toolbar, where
you only disable the Cut, Copy, and Paste controls:

With .CommandBars("Cell")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With
With .CommandBars("Column")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With
With .CommandBars("Row")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With


Whatever you choose to do, don't forget to do the opposite to re-enable
on BeforeClose or Deactivate.


"""AA Arens пиÑал(а):
"""

 

[AA Arens]

Thanks, it works, thanks to all

I missed that one, in fact just thought of it this morning.

There are three command bars - Cell, Column, and Row - that contain the
right-click menu options. You can disable those, to prevent any kind
of right-click menu showing:

.CommandBars("Cell").Enabled = False
.CommandBars("Column").Enabled = False
.CommandBars("Row").Enabled = False

... or you can do the same as we did with the Edit menu toolbar, where
you only disable the Cut, Copy, and Paste controls:

With .CommandBars("Cell")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With
With .CommandBars("Column")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With
With .CommandBars("Row")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With

Whatever you choose to do, don't forget to do the opposite to re-enable
on BeforeClose or Deactivate.

"""AAArensпиÑал(а):
"""