Preserving lock on cells even when copied and pasted within a sheet

P

pdgcss

(Sorry for repeating this post from another group; I inadvertently
neglected adding both groups in my original post in
microsoft.public.excel.misc. In any case, I found a couple of minor
errors and omissions that I have corrected :p.)

Hi,

I am trying to create a *protected* worksheet that is flexible enough
so that additional rows can be created (by copying existing rows from
above) if additional data becomes available to be entered. Locking
rows with formulas and protecting the sheet does allow the user to copy
and paste the cells or rows (most often accomplished by dragging by the
handle); however, the lock is no longer preserved if the destination
rows unlocked to begin with. On the other hand, if the destination
rows are locked, then the user cannot drag (or copy/paste) into them.
I had
assumed that copy and paste (or drag) would preserve cell formats,
including the fact that they are locked, but I guess I was wrong. Users
will be working with either Office 2002 or Office 2003; the worksheet
template is being created in Office 2002.

Thanks for any help you can provide!
 
K

Ken Johnson

Hi,

Locked property is only pasted when the sheet is not protected, so you
might need to use a Worksheet_Change Event Procedure to detect when the
user has entered new data into the next available row. Then it can
unprotect the sheet and copy the last locked row of previously entered
data then paste the formats into the new row, and if there are formulas
to be pasted, paste those into the formula cells in the new row. After
that's done the code can then reapply sheet protection.

I don't know the structure of your worksheet, so all I can do is
demonstrate how it can be achieved on a simple protected worksheet with
column A being where the user would enter new data (either from the
keyboard or pasting in a block of values), and columns B and C have
formulas that depend on the data in column A...

Option Explicit
Public blnDblClk As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
blnDblClk = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iLastRow As Long
iLastRow = Range("B" & _
Range("B:B").Rows.Count).End(xlUp).Row
If Target.Cells(1, 1).Address = _
Cells(iLastRow + 1, 1).Address Then
'Next three lines overcomes the problem of the user
'accidentally double-clicking in the next available
'cell in column A
If blnDblClk And Target.Cells(1, 1) = "" Then
blnDblClk = False
Exit Sub
End If
'Following code runs only after user enters data into the
'next available cell in column A
Dim iRows As Long
Dim rngNewData1 As Range
Dim rngNewData2 As Range
iRows = Target.Rows.Count
Set rngNewData1 = Me.Range(Cells(iLastRow + 1, 1), _
Cells(iLastRow + iRows, 3))
Set rngNewData2 = Me.Range(Cells(iLastRow + 1, 2), _
Cells(iLastRow + iRows, 3))
Application.EnableEvents = False
Me.Unprotect 'If password is used it goes here
Range(Cells(iLastRow, 1), Cells(iLastRow, 3)).Copy
rngNewData1.PasteSpecial xlPasteFormats
Range(Cells(iLastRow, 2), Cells(iLastRow, 3)).Copy
rngNewData2.PasteSpecial xlPasteFormulas
Me.Protect 'if password is used it goes here
Application.EnableEvents = True
End If
End Sub

I've had to use two event procedures because an accidental double
click in the next available cell in column A had an undesirable effect.

If you try this out on a simple worksheet with formatting in columns A,
B and C and formulas in columns B and C that depend on the value in
column A, then you should see that after new values are entered into
column A the formatting and formulas will be copied down the three
columns, and that this only occurs when the new data pasted or typed
into column A starts at its next available cell.

To get the code in place...

1. copy it
2. right click the worksheet's tab then select "View Code" from
the popup menu
3. paste the code into the sheet's code module
4. Save
5. Press Alt + F11 to return to Excel
6. The code will only work if the workbook's Security is set on
Medium and the User clicks "Enable Macros" on the "Security
Warning" dialog that appear when the workbook is opened. To change
the Security to Medium go Tools|Macros|Security...Select
Medium|OK|Close|Open|Click "Enable Macros" on the "Security
Warning dialog.
7. If you have trouble with Users not clicking the "Enable Macros"
button when opening the workbook, it is possible to use more code that
makes the workbook unusable until it is opened with Enabled Macros.

Ken Johnson
 
K

Ken Johnson

Looks like the email has put a line break in the BeforeDoubleClick
procedure heading so for that part of the code paste this instead...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
blnDblClk = True
End Sub

Ken Johnson
 
Top