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

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi,

Locked property is only pasted when the *** 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 *** 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 *** protection.

I don't know the structure of your work***, so all I can do is
demonstrate how it can be achieved on a simple protected work*** 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 work*** 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 work***'s tab then select "View Code" from
the popup menu
3. paste the code into the ***'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

.

Quantcast