Is it possible to make pasting not allowed?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 10 Dec 2006 08:26 GMT

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

Bart
Excel 2003

Reply to this Message

Bob Phillips - 10 Dec 2006 10:03 GMT

Lock the cells as well?

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

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

Reply to this Message

AA Arens - 10 Dec 2006 10:54 GMT

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

> Lock the cells as well?
>
[quoted text clipped - 11 lines]
> > Bart
> > Excel 2003

Reply to this Message

Bob Phillips - 10 Dec 2006 11:25 GMT

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.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> 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
[quoted text clipped - 19 lines]
>> > Bart
>> > Excel 2003

Reply to this Message

AA Arens - 10 Dec 2006 14:37 GMT

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.

> Unprotect the sheet.
> Select all cells and lock them.
[quoted text clipped - 35 lines]
> >> > Bart
> >> > Excel 2003

Reply to this Message

Bob Phillips - 10 Dec 2006 16:49 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Bob,
>
[quoted text clipped - 42 lines]
>> >> > Bart
>> >> > Excel 2003

Reply to this Message

ilia - 11 Dec 2006 10:29 GMT

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.

"""

> You can't, if you want that sort of capability, you need comboboxes rather
> than data validation.
[quoted text clipped - 53 lines]
> >> >> > Bart
> >> >> > Excel 2003

Reply to this Message

AA Arens - 11 Dec 2006 12:56 GMT

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

> 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.
[quoted text clipped - 70 lines]
> > >> >> > Bart
> > >> >> > Excel 2003

Reply to this Message

ilia - 11 Dec 2006 15:42 GMT

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.

> It doesn't work. Find below the VB code
>
[quoted text clipped - 90 lines]
> > > >> >> > Bart
> > > >> >> > Excel 2003

Reply to this Message

AA Arens - 12 Dec 2006 02:14 GMT

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

> Sorry, that was rather incomplete. Apologies.
>
[quoted text clipped - 162 lines]
> > > > >> >> > Bart
> > > > >> >> > Excel 2003

Reply to this Message

ilia - 12 Dec 2006 12:26 GMT

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.

"""

> It works with the code you provided, incl the buttons on the bar. I am
> only still able to use right click > copy/paste.
[quoted text clipped - 165 lines]
> > > > > >> >> > Bart
> > > > > >> >> > Excel 2003

Reply to this Message

AA Arens - 12 Dec 2006 13:28 GMT

Thanks, it works, thanks to all

> I missed that one, in fact just thought of it this morning.
>
[quoted text clipped - 203 lines]
> > > > > > >> >> > Bart
> > > > > > >> >> > Excel 2003

Reply to this Message

ilia - 11 Dec 2006 15:43 GMT

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.

> It doesn't work. Find below the VB code
>
[quoted text clipped - 90 lines]
> > > >> >> > Bart
> > > >> >> > Excel 2003

Reply to this Message

Rate this thread: