I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.

Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.

Rod from Corrections wrote...
....
Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.

Unfortunately, there's no way to disable pasting into any unlocked cell
(sorry, that the correct Excel terminology, even though it means
something quite different where you work).

I've run into this problem myself. It can be solved quite simply with just a
few lines of VB Code. Add this code to your workbook (use Alt-F11 to open
the VB Editor, then double-click "ThisWorkbook", then copy/paste the below
code)

Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Application.CutCopyMode = None
End Sub


NOTE: If the user disables macros when the workbook is opened, then this
won't work.

HTH,
Elkar



"Rod from Corrections" wrote:

I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.

Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.

Elkar
That code produced a "Variable not defined" error on the word "None" in
the Workbokk_Activate macro. Did you leave anything out? Thanks. Otto
"Elkar" wrote in message
...
I've run into this problem myself. It can be solved quite simply with
just a
few lines of VB Code. Add this code to your workbook (use Alt-F11 to open
the VB Editor, then double-click "ThisWorkbook", then copy/paste the below
code)

Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Application.CutCopyMode = None
End Sub


NOTE: If the user disables macros when the workbook is opened, then this
won't work.

HTH,
Elkar



"Rod from Corrections" wrote:

I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to
complete
spreadsheets on line. Although we have heavily protected the worksheets
and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.

Is there a way to turn off the ability to paste data into the worksheet,
and
to force each field to be completed manually? This would eliminate the
problem.

Elkar wrote...
....
Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.CutCopyMode = None
End Sub
....

Aside from the incorrect keyword None (it should be False), this won't
prevent PASTING into Excel from other applications. But maybe it'd
solve the OP's problem.

Hmm... true about pasting from other applications. I guess I haven't really
run into people copying from other apps to Excel, just Excel to Excel.

However, the keyword None works just fine for me at work (Excel 2003) and at
home (Excel XP). Are you using older version? Regardless, False works as
well though.



"Harlan Grove" wrote:

Elkar wrote...
....
Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.CutCopyMode = None
End Sub
....

Aside from the incorrect keyword None (it should be False), this won't
prevent PASTING into Excel from other applications. But maybe it'd
solve the OP's problem.

Otto Moehrbach wrote:
Elkar
That code produced a "Variable not defined" error on the word "None" in
the Workbokk_Activate macro. Did you leave anything out? Thanks. Otto
"Elkar" wrote in message
...


Hi Otto,

I had to use xlNone for it to work.

Ken Johnson

None isn't a keyword in xl2003.

I think you're just using an undeclared variable that is empty (its default
value).

Elkar wrote:

Hmm... true about pasting from other applications. I guess I haven't really
run into people copying from other apps to Excel, just Excel to Excel.

However, the keyword None works just fine for me at work (Excel 2003) and at
home (Excel XP). Are you using older version? Regardless, False works as
well though.

"Harlan Grove" wrote:

Elkar wrote...
....
Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.CutCopyMode = None
End Sub
....

Aside from the incorrect keyword None (it should be False), this won't
prevent PASTING into Excel from other applications. But maybe it'd
solve the OP's problem.



--

Dave Peterson

Elkar, thanks so much for trying to help me. It's nice to know that there
may be a solution to my problem. Unfortunately, when I tried what you
suggested, it had no effect - cut and paste was still allowed.

I know nothing about using Virtual Basic, but did a bit of research. I
tried the debug command and it reflected some syntax errors, which I then
tried to fix.

I ended up with the following in the VB Editor, but it still had no effect.
Can you help? Please be very explicit in your directions, since I am a
complete novice with VB. Thanks!

Private Sub Worksheet_Activate()
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

"Elkar" wrote:

I've run into this problem myself. It can be solved quite simply with just a
few lines of VB Code. Add this code to your workbook (use Alt-F11 to open
the VB Editor, then double-click "ThisWorkbook", then copy/paste the below
code)

Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Application.CutCopyMode = None
End Sub


NOTE: If the user disables macros when the workbook is opened, then this
won't work.

HTH,
Elkar



"Rod from Corrections" wrote:

I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.

Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.

An undeclared variable would seem logical, but I'm not sure that's the case.
I tried this on a clean install of Excel 2003 on a different computer, and
sure enough, None is not recognized. Now I'm going to have to investigate
this further to satisfy my curiosity...

"Dave Peterson" wrote:

None isn't a keyword in xl2003.

I think you're just using an undeclared variable that is empty (its default
value).

Elkar wrote:

Hmm... true about pasting from other applications. I guess I haven't really
run into people copying from other apps to Excel, just Excel to Excel.

However, the keyword None works just fine for me at work (Excel 2003) and at
home (Excel XP). Are you using older version? Regardless, False works as
well though.

"Harlan Grove" wrote:

Elkar wrote...
....
Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.CutCopyMode = None
End Sub
....

Aside from the incorrect keyword None (it should be False), this won't
prevent PASTING into Excel from other applications. But maybe it'd
solve the OP's problem.



--

Dave Peterson

When you say that your code produces no effect, does that mean that you see
the message boxes or not? I tried your code and it produces a message box
every time a new cell is selected.

If you're getting nothing, then I'd check to see that you have Macros enabled.

TOOLS
MACRO
SECURITY
Set Security Level to Medium
Then close the workbook, and reopen.
Choose "Enable Macros"

Do you see results now?

HTH,
Elkar


"Rod from Corrections" wrote:

Elkar, thanks so much for trying to help me. It's nice to know that there
may be a solution to my problem. Unfortunately, when I tried what you
suggested, it had no effect - cut and paste was still allowed.

I know nothing about using Virtual Basic, but did a bit of research. I
tried the debug command and it reflected some syntax errors, which I then
tried to fix.

I ended up with the following in the VB Editor, but it still had no effect.
Can you help? Please be very explicit in your directions, since I am a
complete novice with VB. Thanks!

Private Sub Worksheet_Activate()
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

Strange. I did as you suggested, and did indeed get the message when I
re-opened the file, to which I responded to enable the macros. But then I
was still able to cut and paste, and there was no pop-up message saying that
the cut and paste feature was disabled.

"Elkar" wrote:

When you say that your code produces no effect, does that mean that you see
the message boxes or not? I tried your code and it produces a message box
every time a new cell is selected.

If you're getting nothing, then I'd check to see that you have Macros enabled.

TOOLS
MACRO
SECURITY
Set Security Level to Medium
Then close the workbook, and reopen.
Choose "Enable Macros"

Do you see results now?

HTH,
Elkar


"Rod from Corrections" wrote:

Elkar, thanks so much for trying to help me. It's nice to know that there
may be a solution to my problem. Unfortunately, when I tried what you
suggested, it had no effect - cut and paste was still allowed.

I know nothing about using Virtual Basic, but did a bit of research. I
tried the debug command and it reflected some syntax errors, which I then
tried to fix.

I ended up with the following in the VB Editor, but it still had no effect.
Can you help? Please be very explicit in your directions, since I am a
complete novice with VB. Thanks!

Private Sub Worksheet_Activate()
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

I'd bet that in one of the modules, you have:

Option Explicit

at the top.

This means that you're telling Excel that you must declare your variables. If
you use that statement, your code won't run without declaring the None variable.

I'm guessing on the other pc, you don't have that statement. Excel/VBA assumes
you know what you're doing (in either case!) and treats none like it was
declared "dim None as Variant".

Elkar wrote:

An undeclared variable would seem logical, but I'm not sure that's the case.
I tried this on a clean install of Excel 2003 on a different computer, and
sure enough, None is not recognized. Now I'm going to have to investigate
this further to satisfy my curiosity...

"Dave Peterson" wrote:

None isn't a keyword in xl2003.

I think you're just using an undeclared variable that is empty (its default
value).

Elkar wrote:

Hmm... true about pasting from other applications. I guess I haven't really
run into people copying from other apps to Excel, just Excel to Excel.

However, the keyword None works just fine for me at work (Excel 2003) and at
home (Excel XP). Are you using older version? Regardless, False works as
well though.

"Harlan Grove" wrote:

Elkar wrote...
....
Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Application.CutCopyMode = None
End Sub
....

Aside from the incorrect keyword None (it should be False), this won't
prevent PASTING into Excel from other applications. But maybe it'd
solve the OP's problem.



--

Dave Peterson


--

Dave Peterson

Did you put the code behind ThisWorkbook?

If no, then delete it from where you put it and put it under ThisWorkbook.

Rod from Corrections wrote:

Elkar, thanks so much for trying to help me. It's nice to know that there
may be a solution to my problem. Unfortunately, when I tried what you
suggested, it had no effect - cut and paste was still allowed.

I know nothing about using Virtual Basic, but did a bit of research. I
tried the debug command and it reflected some syntax errors, which I then
tried to fix.

I ended up with the following in the VB Editor, but it still had no effect.
Can you help? Please be very explicit in your directions, since I am a
complete novice with VB. Thanks!

Private Sub Worksheet_Activate()
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Select Case Application.CutCopyMode
Case Is = False
MsgBox "Not in Cut or Copy mode"
Case Is = xlCopy
MsgBox "In Copy mode"
Case Is = xlCut
MsgBox "In Cut mode"
End Select
End Sub

"Elkar" wrote:

I've run into this problem myself. It can be solved quite simply with just a
few lines of VB Code. Add this code to your workbook (use Alt-F11 to open
the VB Editor, then double-click "ThisWorkbook", then copy/paste the below
code)

Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Application.CutCopyMode = None
End Sub


NOTE: If the user disables macros when the workbook is opened, then this
won't work.

HTH,
Elkar



"Rod from Corrections" wrote:

I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.

Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.

--

Dave Peterson

Thanks Ken. Otto
"Ken Johnson" wrote in message
oups.com...

Otto Moehrbach wrote:
Elkar
That code produced a "Variable not defined" error on the word "None"
in
the Workbokk_Activate macro. Did you leave anything out? Thanks. Otto
"Elkar" wrote in message
...


Hi Otto,

I had to use xlNone for it to work.

Ken Johnson

Otto Moehrbach wrote:
Thanks Ken. Otto

Hi Otto,

You're welcome.

Ken Johnson

"Rod from Corrections" wrote:

I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.

Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.