Pasting from Word locks cells

[Andy]

Hi

Hopefully this makes sense:

I have a Worksheet that contains several macro's that allow the user to
insert rows, move groups of rows up/down etc.

There is only one area of the worksheet that users need to update (say
that is between rows 6 - 30). To avoid users accidently deleting a row
or inserting a row in the areas outside of rows 6 - 30, all the other
cells are locked and the worksheet is protected.

In order to run the macros, password protect is turned off in the vb
code and turned on again at the end of the sub. To ensure that the
insert row etc. is not being performed outside of the set area, the code
contains:

If Selection.Locked = True Then

Error Message

Else

Continue with Code

This seems to work fine. A problem occurs however when the user pastes
text from Word or an email into the spreadsheet (within Rows 6-30) as
the locked cell check box becomes ticked and this stops the macros from
working correctly.

I found that if they paste special and choose text, this does not
happen, the cell remains unlocked.

Any idea why the cell locks when text is pasted from another application?

Any ideas as to another way round it?

The cells that can be updated have no background colour (ie. White),
whereas cells that contain formulas etc are pale yellow. How would I
code that so it basically says:

If Selection.Background Colour <> White Then

Error Message

Else

Continue with Code

My ramble has now ended.

Thanks

Andy

 

[goober]

Try adding this code right before you check for locked cells. As Exce
sees the pasted cells as new, by default it sets them as protected.
This code will change the cells defined in the range to unlocked. I
will also change the color of all cells with a formula in that sam
range to a pale yellow.


Range("A6:A30").Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With


Good Luck