Can you store clipboard text and pick it up by copy it again?

[Imbecill]

I have a catch 22 - impossible actions here:

A/ I want to have the sheet protected so users can't ruin all cells in the
sheet
B/ I have to Unprotect the sheet in the sub event Worksheet_Activate to be
able to do some format macros
C/ I want users to allow to copy text from one sheet and paste in this one.
D/ The clipboard wipes out by the Unprotect command
E/ The user have nothing to paste in...

Is there a solution?

/Regards

---------------------------------------------------------------
------------------------- Earl Kiosterud wrote: ---------------

Imbecill,

CutCopyMode gets turned off my many things when the clipboard contains an
Excel object. To see this, try doing a manual copy, then select a cell,
enter something, and press Enter. Clipboard cleared. When the clipboard
contains other stuff, like text or a graphic, this doesn't happen

You can step through your code with F8, Alt-Tabbing back to Excel to see
where the marching ants around the copied area go away. That's where it
happened. I think you'll have to change your code around and go copy the
stuff just before you want to paste it. The usual construct to do it all at
once is:

Range(MyRange).Copy Destination:=Range("E5")

--
Earl Kiosterud
mvpearl omitthisword at verizon period net

---------------------------------------------------------------
------------------------- Imbecill wrote: ----------------------

Hello,

My problem is that in my code I unprotect the sheet and furter down the sub,
I try to pastespecial. However, the clipboard go empty when
ActiveSheet.Unprotect come...

I can't "paste first" or in an other way change order, because the
pastespecial is generated by a Application.OnKey "^v", "FormulaPaste"
and the Sheet.Unprotect is inside an event witin another macro that starts
when you activate the sheet.

Any solution? Ive searched everywere ...

 

[Dave Peterson]

Maybe a macro that does the work for them:

Option Explicit
Sub testme01()

Dim rngF As Range
Dim rngT As Range
Dim wks As Worksheet
Dim wasProtected As Boolean

Set wks = Worksheets("sheet1")

Set rngF = Nothing
Set rngT = Nothing
On Error Resume Next
Set rngF = Application.InputBox(prompt:="Copy from where", _
Default:=Selection.Address(0, 0), Type:=8)
If rngF Is Nothing Then
Exit Sub
End If
Set rngT = Application.InputBox(prompt:="Where to paste", _
Type:=8).Cells(1, 1)
If rngT Is Nothing Then
Exit Sub
End If
On Error GoTo 0

On Error GoTo errHandler:
If rngT.Parent.Name = wks.Name Then
If wks.ProtectContents Then
wks.Unprotect Password:="hi"
wasProtected = True
End If
rngF.Copy _
Destination:=rngT
Application.Goto reference:=rngT
End If

errHandler:
If wasProtected Then
wks.Protect Password:="hi"
End If
End Sub