Hello,

I am trying to copy a value onto the clipboard for pasting into another
application. I "Googled" a newsgroup and found the following code kindly
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 protection
is in place, I get a Run time 1004 error: Application-defined or
object-defined error and the highlight appears over the code section:If
rCell.FormulaR1C1 < "" Then ...

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

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

I would greatly appreciate any assistance.


--
Potoroo


------------------------------------------------------------------------
Potoroo's Profile: http://www.excelforum.com/member.php...o&userid=29958
View this thread: http://www.excelforum.com/showthread...hreadid=496578

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 wrote:

Hello,

I am trying to copy a value onto the clipboard for pasting into another
application. I "Googled" a newsgroup and found the following code kindly
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 protection
is in place, I get a Run time 1004 error: Application-defined or
object-defined error and the highlight appears over the code section:If
rCell.FormulaR1C1 < "" Then ...

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

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

I would greatly appreciate any assistance.

--
Potoroo

------------------------------------------------------------------------
Potoroo's Profile: http://www.excelforum.com/member.php...o&userid=29958
View this thread: http://www.excelforum.com/showthread...hreadid=496578

--

Dave Peterson

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!


--
Potoroo


------------------------------------------------------------------------
Potoroo's Profile: http://www.excelforum.com/member.php...o&userid=29958
View this thread: http://www.excelforum.com/showthread...hreadid=496578