Run time error 1004

[Potoroo]

Hello,

I am trying to copy a value onto the clipboard for pasting into anothe
application. I "Googled" a newsgroup and found the following code kindl
provided by a contributor called "quartz":
Sub TestThis()
Dim rCell As Range
Dim sData As String
For Each rCell In Selection
If rCell.FormulaR1C1 <> "" Then sData = sData
Left(rCell.FormulaR1C1, 5) & "|"
Next
sData = Left(sData, Len(sData) - 1)
Call ClipboardAddString(sData)
MsgBox sData
End Sub

Public Function ClipboardAddString(argString As String)
'REQUIRED: REFERENCE TO MICROSOFT FORMS 2.0 OBJECT LIBRARY
'PROGRAMMATICALLY PLACE DATA IN THE CLIPBOARD;
Dim objData As DataObject
Set objData = New DataObject
objData.SetText argString
objData.PutInClipboard
End Function

This works beautifully until I protect the sheet. Once the protectio
is in place, I get a Run time 1004 error: Application-defined o
object-defined error and the highlight appears over the code section:I
rCell.FormulaR1C1 <> "" Then ...

It is always the same cell I want to copy the value from (in this cas
it's Z100). In the cell properties, I have unticked the box fo
"Locked" but have left the "Hidden" box ticked.

Is it possible to copy the value from a cell to the clipboard with th
sheet protected? I need to protect it as it forms part of
"calculator" which will cease to operate effectively if any of th
currently protected cells are overwritten.

I would greatly appreciate any assistance

 

[Dave Peterson]

I got the same result if I locked the cell and checked hidden (and then
protected the worksheet). But if the formula is supposed to be hidden, then it
doesn't seem to surprising that even code can't get to it.

But if I locked the cell and left the hidden box unchecked (worksheet was still
protected), then the code worked fine.

If you need the formula hidden, then maybe your code could unprotect the
worksheet, do its work, then reprotect the worksheet.

 

[Potoroo]

Thanks Dave! That did the trick. I think I had tried just about every
other combination but it's hard to remember where you've been sometimes
when there are constant interruptions.

I've moved the info to a different cell location and formatted it so it
just looks invisible and left it unhidden and unlocked.

Thanks again. The advice and knowledge you guys share is simply amazing
and truly appreciated.

Have a great 2006!