prevent pasting to more than one row

  • Thread starter Thread starter Brotherwarren
  • Start date Start date
B

Brotherwarren

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" <[email protected]>
wrote in message
news:1696bd07-fcfe-4b12-9091-5e994ece774e@q14g2000prh.googlegroups.com...
 
Right-click the sheet tab, choose 'View Code' and paste the following VBAcode 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" <[email protected]>
wrote in message
Click to expand...

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.
 
Back
Top