how to copy and paste in a protected worksheet

[George]

Ladies and Gentlemen:

In my worksheet, I have a column with all cells in that column locked
(It is a must and I CAN NOT unlock any cell in that column). There are
a few cells UNDER OTHER COLUMNS unlocked for user to input their data.
Then the whole worksheet is password-protected. My question is how to
allow user to copy one ROW and paste it into another row without
unprotect the worksheet? Please keep it in mind that there is at least
one cell in EACH ROW locked where user CAN NOT modify the value in that
cell.

Thanks for your help!

George

[mrice]

One way would be to provide a special version of the copy function which, in the background, unprotected the sheet, did the transformation and then reapplied the protection.

You would also need to protect the macro itself to prevent the user running it in break mode.

[George]

Appriciate. But I don't know how. Could you please show me in more
details?

Thanks for your help!

George
**************************
mrice wrote:
> One way would be to provide a special version of the copy function
> which, in the background, unprotected the sheet, did the transformation
> and then reapplied the protection.
>
> You would also need to protect the macro itself to prevent the user
> running it in break mode.
>
>
> --
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
> View this thread: http://www.excelforum.com/showthread...hreadid=571191

[CaptainQuattro]

I have written the following macro that copies from all the unprotected cells in the active cell row and pastes to the corresponding cells in another row.

The benefit of doing this as opposed to simply unlocking the destination range, pasting and locking it again is that there is no guarantee that the locked cells in the destination range are blank or contain exact copies of the formulas in the source range.

DISCLAIMER/WARNING:

This is brand new code, so please use extreme caution, and make multiple backups of your file before you start to test.

Option Explicit
Sub CopyUnlocked()

Dim DestRow As Integer

DestRow = Application.InputBox(Prompt:="Enter Destination Row", Title:="DestinationRow", Type:=1)
Dim c As Range
Dim DestRange As Range
Dim rng As Range


Range("$A$" & ActiveCell.Row).Select
Set c = ActiveCell

If (c.Locked) Then
Do Until Not (c.Locked)

If c.Column = 256 Then
Exit Do
End If

Set c = c.Offset(0, 1)
Loop
End If




Do While c.Column < 257

If (c.Locked) Then
Do Until Not (c.Locked)
If c.Column = 256 Then
Exit Do
End If
Set c = c.Offset(0, 1)
Loop
End If

Set rng = c
Set DestRange = c.Offset(DestRow - c.Row, 0)

Do Until (c.Locked)

Set c = c.Offset(0, 1)

If (c.Locked) Then
Exit Do ' Exit the do until loop
End If


Set rng = Union(rng, c)

If c.Column = 256 Then
Exit Do ' exit the do until loop
End If


Loop


rng.Copy
DestRange.PasteSpecial xlPasteValues
Range("$A$" & DestRange.Row).Select

If c.Column = 256 Then
Exit Do ' exit the do WHILE loop
End If

Set c = c.Offset(0, 1)
Set rng = c
Set DestRange = c.Offset(DestRow - c.Row, 0)


Loop
End Sub

[George]

Hi, CaptainQuattro:

I test your code, but it doesn't work. It show RED with the part of "
DestRow = Application.InputBox (Prompt:="Enter Destination Row",
Title:="DestinationRow", Type:=1)

Could you please test it from your excel worksheet? For example, unlock
all cells under COLUMN A and lock all cells under COLUMN B, input
random numbers in row 1, then protect the worksheet with password
"CAPTAIN". Save, close and re-open the worksheet. Try to copy row 1 and
paste it into row 2.

Thank you so much. I look forward to hearing from you soon!

George
*******************************************************
CaptainQuattro wrote:
> I have written the following macro that copies from all the unprotected
> cells in the active cell row and pastes to the corresponding cells in
> another row.
>
> The benefit of doing this as opposed to simply unlocking the
> destination range, pasting and locking it again is that there is no
> guarantee that the locked cells in the destination range are blank or
> contain exact copies of the formulas in the source range.
>
> DISCLAIMER/WARNING:
>
> THIS IS BRAND NEW CODE, SO PLEASE USE EXTREME CAUTION, AND MAKE
> MULTIPLE BACKUPS OF YOUR FILE BEFORE YOU START TO TEST.
>
> Option Explicit
> Sub CopyUnlocked()
>
> Dim DestRow As Integer
>
> DestRow = Application.InputBox(Prompt:="Enter Destination Row",
> Title:="DestinationRow", Type:=1)
> Dim c As Range
> Dim DestRange As Range
> Dim rng As Range
>
>
> Range("$A$" & ActiveCell.Row).Select
> Set c = ActiveCell
>
> If (c.Locked) Then
> Do Until Not (c.Locked)
>
> If c.Column = 256 Then
> Exit Do
> End If
>
> Set c = c.Offset(0, 1)
> Loop
> End If
>
>
>
>
> Do While c.Column < 257
>
> If (c.Locked) Then
> Do Until Not (c.Locked)
> If c.Column = 256 Then
> Exit Do
> End If
> Set c = c.Offset(0, 1)
> Loop
> End If
>
> Set rng = c
> Set DestRange = c.Offset(DestRow - c.Row, 0)
>
> Do Until (c.Locked)
>
> Set c = c.Offset(0, 1)
>
> If (c.Locked) Then
> Exit Do ' Exit the do until loop
> End If
>
>
> Set rng = Union(rng, c)
>
> If c.Column = 256 Then
> Exit Do ' exit the do until loop
> End If
>
>
> Loop
>
>
> rng.Copy
> DestRange.PasteSpecial xlPasteValues
> Range("$A$" & DestRange.Row).Select
>
> If c.Column = 256 Then
> Exit Do ' exit the do WHILE loop
> End If
>
> Set c = c.Offset(0, 1)
> Set rng = c
> Set DestRange = c.Offset(DestRow - c.Row, 0)
>
>
> Loop
> End Sub
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=571191

[CaptainQuattro]

George:

I tested the macro by

copying the code back from my posting to a new workbook (Just in case something got messed up in the process of posting it.)

Unlocking column A
Unlocking column C through IV
Protecting the worksheet
Saving the workbook, closing and re-loading it.

It worked for me under these conditions.

If anyone has any ideas to improve this macro, please feel free to dive in.

Regards.

[George]

Hi, CaptainQuattro:

I need to buy you a meal if you are in Houston to thank you for your
time and efforts in helping me out.We are close but not yet. Here is
what I did:

1) open a new workbook called Book 1, create a new macro button called
"CopyUnlocked", copy and paste your code into that module 1, hit Alt+Q
to go back to excel;
2) lock all cells under column B and unlock all cells under any other
columns;
3) put 11 in cell A1, 22 in cell B1, 33 in cell C1, put 999 in cell
IV1;
4) protect "sheet1" by a password;
5) Save, close and re-open the file;
6) position the cursor to row 1 and right click it, select function
"copy" to copy the WHOLE ROW 1, click the macro button we just made,
enter 2 to respond the message box "Enter Destination Row". It does
copy values in the unlocked cells in row 1 and paste it into the
corresponding cells in row 2. The problem is I also want to copy values
in locked cells in row 1 (B1 in our example) and paste it into row 2
(B2).

How to solve this problem? Remember that my task is to copy the whole
row 1 and paste it into whole row 2 no matter the cells in row 1 or row
2 are locked or not. This means the information in row 1 and row 2 are
identical from column A throught column IV.

Again, I appreciate your response and look forward to hearing from you
soon! Please keep it in mind taht I reserve a meal for you no matter
you can figure out this or not! If you would like, I can e-mail you my
contact information with my telephone number in.

Thanks again and have a great day!

George
**************************************************************************************************************


George:
>
> I tested the macro by
>
> copying the code back from my posting to a new workbook (Just in case
> something got messed up in the process of posting it.)
>
> Unlocking column A
> Unlocking column C through IV
> Protecting the worksheet
> Saving the workbook, closing and re-loading it.
>
> It worked for me under these conditions.
>
> If anyone has any ideas to improve this macro, please feel free to dive
> in.
>
> Regards.
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=571191

[George]

Hi, CaptainQuattro:

Sorry, one more point:

What if I want to copy from another workbook or worksheet and paste the
information into this protected worksheet? The information need to
paste might be in the locked cell.

Thanks,

George
************************************************************************************************
George wrote:
> Hi, CaptainQuattro:
>
> I need to buy you a meal if you are in Houston to thank you for your
> time and efforts in helping me out.We are close but not yet. Here is
> what I did:
>
> 1) open a new workbook called Book 1, create a new macro button called
> "CopyUnlocked", copy and paste your code into that module 1, hit Alt+Q
> to go back to excel;
> 2) lock all cells under column B and unlock all cells under any other
> columns;
> 3) put 11 in cell A1, 22 in cell B1, 33 in cell C1, put 999 in cell
> IV1;
> 4) protect "sheet1" by a password;
> 5) Save, close and re-open the file;
> 6) position the cursor to row 1 and right click it, select function
> "copy" to copy the WHOLE ROW 1, click the macro button we just made,
> enter 2 to respond the message box "Enter Destination Row". It does
> copy values in the unlocked cells in row 1 and paste it into the
> corresponding cells in row 2. The problem is I also want to copy values
> in locked cells in row 1 (B1 in our example) and paste it into row 2
> (B2).
>
> How to solve this problem? Remember that my task is to copy the whole
> row 1 and paste it into whole row 2 no matter the cells in row 1 or row
> 2 are locked or not. This means the information in row 1 and row 2 are
> identical from column A throught column IV.
>
> Again, I appreciate your response and look forward to hearing from you
> soon! Please keep it in mind taht I reserve a meal for you no matter
> you can figure out this or not! If you would like, I can e-mail you my
> contact information with my telephone number in.
>
> Thanks again and have a great day!
>
> George
> **************************************************************************************************************
>
>
> George:
> >
> > I tested the macro by
> >
> > copying the code back from my posting to a new workbook (Just in case
> > something got messed up in the process of posting it.)
> >
> > Unlocking column A
> > Unlocking column C through IV
> > Protecting the worksheet
> > Saving the workbook, closing and re-loading it.
> >
> > It worked for me under these conditions.
> >
> > If anyone has any ideas to improve this macro, please feel free to dive
> > in.
> >
> > Regards.
> >
> >
> > --
> > CaptainQuattro
> > ------------------------------------------------------------------------
> > CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> > View this thread: http://www.excelforum.com/showthread...hreadid=571191

[CaptainQuattro]

Ok George, I think I've got it.

Martin Rice was right that you need a macro that unlocks and re-locks the sheet.


I have written 2 new macros: One for copying and pasting within the same worksheet, and the other for copying from an external worksheet to the current sheet.

It occurred to me that there is a danger of accidentally pasting over a row of good data, therefore I have included the following protection:

Column IV can be unlocked.
On each row of good data, column IV should contain the letter "P" (uppercase). The macros will not paste onto a row that contains this flag in column IV. If the user needs to override this feature, he or she can do so by clearing the 'P' on that row.

If you don't want to trust the users, you can leave column IV locked so that the users have to call you to unprotect the worksheet and clear the P.

Because the code includes the password to unprotect the sheet, you should paste the code into a button from the Control toolbox toolbar, not from the Forms toolbar. When the sheet is protected it will not be possible for users to right click the button and see the code.

After creating the buttons, click on each one's properties and change the names to CopyRow and CopyExternalRow.

Option Explicit

Private Sub CopyExternalRow_Click()
ActiveSheet.Protect Password:="captain"
On Error GoTo errhandle

Dim iSourceRow As Integer
Dim iDestRow As Integer
Dim sDestRow As String
Dim sSourceRow As String
Dim sSourceBook As String

Dim sSourceSheet As String
Dim sDestBook As String

sDestBook = ThisWorkbook.Name
sSourceBook = Application.InputBox(Prompt:="Enter Workbook to copy from" & Chr$(13) & "do not include file extension (e.g. .xls)", Title:="SourceBook", Type:=2) & ".xls"
sSourceSheet = Application.InputBox(Prompt:="Enter Sheet to copy from", Title:="SourceSheet", Type:=2)
iSourceRow = Application.InputBox(Prompt:="Enter Row to copy from", Title:="SourceRow", Type:=1)
sSourceRow = "$" & iSourceRow & ":$" & iSourceRow
iDestRow = Application.InputBox(Prompt:="Enter Destination Row", Title:="DestinationRow", Type:=1)
sDestRow = "$" & iDestRow & ":$" & iDestRow


'To prevent accidentally overwriting existing data, column IV of the worksheet should be unprotected
'and you should enter the letter P in column IV of each row that you want to protect. The macro will
'automatically enter a 'P' in column IV of your destination row.

If Range("IV" & iDestRow).Value = "P" Then
MsgBox "Row " & iDestRow & " is marked as protected." & Chr$(13) & "Clear cell IV" & iDestRow & " to overwrite contents of row " & iDestRow
End

Else
End If



ActiveSheet.Unprotect Password:="captain"
Windows(sSourceBook).Activate
ActiveWorkbook.Sheets(sSourceSheet).Range(sSourceRow).Copy
Windows(sDestBook).Activate


Range(sDestRow).PasteSpecial xlPasteAll
Range("IV" & iDestRow).Value = "P"

errhandle:
ActiveSheet.Protect Password:="captain"

End Sub


Private Sub CopyRow_Click()
ActiveSheet.Protect Password:="captain"
On Error GoTo errhandle

Dim iDestRow As Integer
Dim sDestRow As String
Dim sSourceRow As String


sSourceRow = "$" & ActiveCell.Row & ":$" & ActiveCell.Row
iDestRow = Application.InputBox(Prompt:="Enter Destination Row", Title:="DestinationRow", Type:=1)

'To prevent accidentally overwriting existing data, column IV of the worksheet should be unprotected
'and you should enter the letter P in column IV of each row that you want to protect. The macro will
'automatically enter a 'P' in column IV of your destination row.

If Range("IV" & iDestRow).Value = "P" Then
MsgBox "Row " & iDestRow & " is marked as protected." & Chr$(13) & "Clear cell IV" & iDestRow & " to overwrite contents of row " & iDestRow
End

Else
End If

sDestRow = "$" & iDestRow & ":$" & iDestRow

ActiveSheet.Unprotect Password:="captain"

Range(sSourceRow).Copy
Range(sDestRow).PasteSpecial xlPasteAll
Range("IV" & iDestRow).Value = "P"

errhandle:
ActiveSheet.Protect Password:="captain"

End Sub

[George]

Hi, CaptainQauttro:

You are really knowledgable in Excel. However, nothing happens after I
tested your code under the procedures you specified. Would you please
test it to see where is the problem? You might do it already before you
post this message.

In the meantime, it might be a good idea that we just hide several
columns without protect the worksheet (for example, from column H to
Column IV) such that user CAN NOT unhide those hidden columns. This
means that Column A to column G is the ONLY area for user to MANUALLY
input their stuff. In saying so, I still want to allow user to

1) insert a blank row;
2) delete any row he or she does not need;
3) copy a whole row (say row 1 which certainlly includes hidden
contents) both internally and externally, and paste the information
onto another row.

If we can achieve the above successfully, we do not need to worry about
the worksheet protection problem. But I might be totally wrong. How do
you think about it? Is there anyway we can do this?

My e-mail is [email protected] and my telephone number is (713)948-4025.
You can call me or e-mail me anytime you want. It will not bother at
all. I trust you are an excellent person doing great in everything!

Thank again for your time and efforts. Best wishes to you and your
family!

George
*******************************************************************



CaptainQuattro wrote:
> Ok George, I think I've got it.
>
> Martin Rice was right that you need a macro that unlocks and re-locks
> the sheet.
>
>
> I have written 2 new macros: One for copying and pasting within the
> same worksheet, and the other for copying from an external worksheet to
> the current sheet.
>
> It occurred to me that there is a danger of accidentally pasting over a
> row of good data, therefore I have included the following protection:
>
> Column IV can be unlocked.
> On each row of good data, column IV should contain the letter "P"
> (uppercase). The macros will not paste onto a row that contains this
> flag in column IV. If the user needs to override this feature, he or
> she can do so by clearing the 'P' on that row.
>
> If you don't want to trust the users, you can leave column IV locked so
> that the users have to call you to unprotect the worksheet and clear the
> P.
>
> Because the code includes the password to unprotect the sheet, you
> should paste the code into a button from the Control toolbox toolbar,
> not from the Forms toolbar. When the sheet is protected it will not be
> possible for users to right click the button and see the code.
>
> After creating the buttons, click on each one's properties and change
> the names to CopyRow and CopyExternalRow.
>
> Option Explicit
>
> Private Sub CopyExternalRow_Click()
> ActiveSheet.Protect Password:="captain"
> On Error GoTo errhandle
>
> Dim iSourceRow As Integer
> Dim iDestRow As Integer
> Dim sDestRow As String
> Dim sSourceRow As String
> Dim sSourceBook As String
>
> Dim sSourceSheet As String
> Dim sDestBook As String
>
> sDestBook = ThisWorkbook.Name
> sSourceBook = Application.InputBox(Prompt:="Enter Workbook to copy
> from" & Chr$(13) & "do not include file extension (e.g. .xls)",
> Title:="SourceBook", Type:=2) & ".xls"
> sSourceSheet = Application.InputBox(Prompt:="Enter Sheet to copy
> from", Title:="SourceSheet", Type:=2)
> iSourceRow = Application.InputBox(Prompt:="Enter Row to copy from",
> Title:="SourceRow", Type:=1)
> sSourceRow = "$" & iSourceRow & ":$" & iSourceRow
> iDestRow = Application.InputBox(Prompt:="Enter Destination Row",
> Title:="DestinationRow", Type:=1)
> sDestRow = "$" & iDestRow & ":$" & iDestRow
>
>
> 'To prevent accidentally overwriting existing data, column IV of
> the worksheet should be unprotected
> 'and you should enter the letter P in column IV of each row that
> you want to protect. The macro will
> 'automatically enter a 'P' in column IV of your destination row.
>
> If Range("IV" & iDestRow).Value = "P" Then
> MsgBox "Row " & iDestRow & " is marked as protected." & Chr$(13) &
> "Clear cell IV" & iDestRow & " to overwrite contents of row " &
> iDestRow
> End
>
> Else
> End If
>
>
>
> ActiveSheet.Unprotect Password:="captain"
> Windows(sSourceBook).Activate
> ActiveWorkbook.Sheets(sSourceSheet).Range(sSourceRow).Copy
> Windows(sDestBook).Activate
>
>
> Range(sDestRow).PasteSpecial xlPasteAll
> Range("IV" & iDestRow).Value = "P"
>
> errhandle:
> ActiveSheet.Protect Password:="captain"
>
> End Sub
>
>
> Private Sub CopyRow_Click()
> ActiveSheet.Protect Password:="captain"
> On Error GoTo errhandle
>
> Dim iDestRow As Integer
> Dim sDestRow As String
> Dim sSourceRow As String
>
>
> sSourceRow = "$" & ActiveCell.Row & ":$" & ActiveCell.Row
> iDestRow = Application.InputBox(Prompt:="Enter Destination Row",
> Title:="DestinationRow", Type:=1)
>
> 'To prevent accidentally overwriting existing data, column IV of
> the worksheet should be unprotected
> 'and you should enter the letter P in column IV of each row that
> you want to protect. The macro will
> 'automatically enter a 'P' in column IV of your destination row.
>
> If Range("IV" & iDestRow).Value = "P" Then
> MsgBox "Row " & iDestRow & " is marked as protected." & Chr$(13) &
> "Clear cell IV" & iDestRow & " to overwrite contents of row " &
> iDestRow
> End
>
> Else
> End If
>
> sDestRow = "$" & iDestRow & ":$" & iDestRow
>
> ActiveSheet.Unprotect Password:="captain"
>
> Range(sSourceRow).Copy
> Range(sDestRow).PasteSpecial xlPasteAll
> Range("IV" & iDestRow).Value = "P"
>
> errhandle:
> ActiveSheet.Protect Password:="captain"
>
> End Sub
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=571191

[CaptainQuattro]

George:

I will send you a copy of my working model.

But it sounds as if the following would work better for you:

Have a separate, completely unprotected sheet for user input and a fully protected sheet that uses formulas to reflect what the user has input.

If you use =OFFSET formulas on your protected sheet, the users will be free to insert and delete rows and columns on the input sheet at will without corrupting the protected sheet.

For example, enter the following formula in cell A1 of Sheet1:

=OFFSET(Sheet2!$A$1,ROWS($A$1:D3)-1,COLUMNS($A$1:D3)-1)

and copy this formula to the range A1:H20000

Enter whatever other formulas you need in columns I through IV

Use Tools > Options > View and un-check Zero values.

Protect Sheet1 with password.

Hope this helps.

[George]

Hi, CaptainQuattro:

Thank you so much for the quick response. I will test it tomorrow and
let you know the result! Good night!

Best personal regards,

George
************************************
CaptainQuattro wrote:
> George:
>
> I will send you a copy of my working model.
>
> But it sounds as if the following would work better for you:
>
> Have a separate, completely unprotected sheet for user input and a
> fully protected sheet that uses formulas to reflect what the user has
> input.
>
> If you use =OFFSET formulas on your protected sheet, the users will be
> free to insert and delete rows and columns on the input sheet at will
> without corrupting the protected sheet.
>
> For example, enter the following formula in cell A1 of Sheet1:
>
> =OFFSET(Sheet2!$A$1,ROWS($A$1:D3)-1,COLUMNS($A$1:D3)-1)
>
> and copy this formula to the range A1:H20000
>
> Enter whatever other formulas you need in columns I through IV
>
> Use Tools > Options > View and un-check Zero values.
>
> Protect Sheet1 with password.
>
> Hope this helps.
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=571191

[George]

Hi, CaptainQuattro:

I sent you an attachment to your personal mail box. Whenever you have a
chance, please take a look.

Thank you so much!

George
*******************************
George wrote:
> Hi, CaptainQuattro:
>
> Thank you so much for the quick response. I will test it tomorrow and
> let you know the result! Good night!
>
> Best personal regards,
>
> George
> ************************************
> CaptainQuattro wrote:
> > George:
> >
> > I will send you a copy of my working model.
> >
> > But it sounds as if the following would work better for you:
> >
> > Have a separate, completely unprotected sheet for user input and a
> > fully protected sheet that uses formulas to reflect what the user has
> > input.
> >
> > If you use =OFFSET formulas on your protected sheet, the users will be
> > free to insert and delete rows and columns on the input sheet at will
> > without corrupting the protected sheet.
> >
> > For example, enter the following formula in cell A1 of Sheet1:
> >
> > =OFFSET(Sheet2!$A$1,ROWS($A$1:D3)-1,COLUMNS($A$1:D3)-1)
> >
> > and copy this formula to the range A1:H20000
> >
> > Enter whatever other formulas you need in columns I through IV
> >
> > Use Tools > Options > View and un-check Zero values.
> >
> > Protect Sheet1 with password.
> >
> > Hope this helps.
> >
> >
> > --
> > CaptainQuattro
> > ------------------------------------------------------------------------
> > CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> > View this thread: http://www.excelforum.com/showthread...hreadid=571191