Hi folks,

I've hit a bit of a wall and can't find a solution to the
following. . .

I have a shared workbook, in excel 2007.

The workbook contains a list of pupils' names, along with the name of
the class that they are enrolled in.

Users (teachers) select their own class name from a front page, a
macro then autofilters the list of pupils to show only those taught by
that teacher.

The whole sheet is locked, then only the visible cells are unlocked.
This is to prevent staff overwriting comments previously written.

Teachers type reports into the cells and then save them.

The problem is . . .
Many teachers prefer to type their reports in word, and copy and paste
them over to excel.

If they include line breaks or carriage returns in their report then
excel splits their comment over two cells. This isn't a problem as
usually the row below the one they are pasting to is locked.
Occasionally though staff will teach two students who are adjacent in
the class list and can overwrite the comment in the cell below the one
the are pasting to.

so. . .

Is there a way to prevent users pasting linebreaks or carriage
returns?

Many thanks
Tony

Right-click the sheet tab, choose 'View Code' and paste the following VBA code into the big window
on the right side...

'---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ThingsChange
If Target.Cells.Count 1 Then
Application.EnableEvents = False
MsgBox "Change only one cell at a time or forfeit your tenure. ", , "Blame Tony"
Application.Undo
End If
ThingsChange:
Application.EnableEvents = True
End Sub
'---

You should test/experiment with it first in a separate workbook.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)



"Brotherwarren"
wrote in message
...
Hi folks,

I've hit a bit of a wall and can't find a solution to the
following. . .
I have a shared workbook, in excel 2007.
The workbook contains a list of pupils' names, along with the name of
the class that they are enrolled in.
Users (teachers) select their own class name from a front page, a
macro then autofilters the list of pupils to show only those taught by
that teacher.
The whole sheet is locked, then only the visible cells are unlocked.
This is to prevent staff overwriting comments previously written.
Teachers type reports into the cells and then save them.
The problem is . . .
Many teachers prefer to type their reports in word, and copy and paste
them over to excel.
If they include line breaks or carriage returns in their report then
excel splits their comment over two cells. This isn't a problem as
usually the row below the one they are pasting to is locked.
Occasionally though staff will teach two students who are adjacent in
the class list and can overwrite the comment in the cell below the one
the are pasting to.
so. . .
Is there a way to prevent users pasting linebreaks or carriage
returns?
Many thanks
Tony

On May 13, 4:09*pm, "Jim Cone" wrote:
Right-click the sheet tab, choose 'View Code' and paste the following VBA code into the big window
on the right side...

'---
Private Sub Worksheet_Change(ByVal Target As Range)
*On Error GoTo ThingsChange
*If Target.Cells.Count 1 Then
* * Application.EnableEvents = False
* * MsgBox "Change only one cell at a time or forfeit your tenure. * ", , "Blame Tony"
* * Application.Undo
*End If
ThingsChange:
*Application.EnableEvents = True
End Sub
'---

You should test/experiment with it first in a separate workbook.
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"Brotherwarren"
wrote in ...



Hi folks,

I've hit a bit of a wall and can't find a solution to the
following. . .
I have a shared workbook, in excel 2007.
The workbook contains a list of pupils' names, along with the name of
the class that they are enrolled in.
Users (teachers) select their own class name from a front page, a
macro then autofilters the list of pupils to show only those taught by
that teacher.
The whole sheet is locked, then only the visible cells are unlocked.
This is to prevent staff overwriting comments previously written.
Teachers type reports into the cells and then save them.
The problem is *. . .
Many teachers prefer to type their reports in word, and copy and paste
them over to excel.
If they include line breaks or carriage returns in their report then
excel splits their comment over two cells. *This isn't a problem as
usually the row below the one they are pasting to is locked.
Occasionally though staff will teach two students who are adjacent in
the class list and can overwrite the comment in the cell below the one
the are pasting to.
so. . .
Is there a way to prevent users pasting linebreaks or carriage
returns?
Many thanks
Tony

Aahhh!!! So simple, thank you.

.. . . Forfeit your tenure . . . I lol'd

Thank you for you help. I'll test it out when I'm back at work on
Monday.