Hi Guys,

I have a worksheet in which the cells are protected (userinterfaceonly)
which prevents the user from editing a cells contents.
However, for some cells, I would like them to be able to copy the contents
to the clipboard in order for them to work with it elsewhere.
Ive achieved this with a commandbutton which the user clicks to copy the
cell contents.

Code for commandbutton
Private Sub CommandButton1_Click()
Dim c As New DataObject
c.SetText Range("A" & ActiveCell.Row).Text
c.PutInClipboard
End Sub

The problem occurs if the cell content is quite long. It seems that there is
a limit to how much can be copied. Given that its possible to copy vast
quantities to the clipboard manually, I cant work out why this is.
To test this I took a blank sheet and in column A, rows 2 to 20 I placed
strings ranging in length from 20 to 2090 characters. I then added a button
and used it to copy the strings to the clipboard. I then pasted the clipboard
contents in an adjacent cell and compared the string lengths.
Ive discovered that the maximum number of characters that and be copied to
the clipboard is 1024. If you try and use the above code to copy a string
which is =1025 characters, when you paste the result it's only the first
1024!

Does anyone know why this is or how to get around it?

Many thanks in advance

The reason it is, is that Microsoft designed it that way. One way around it
is to do a direct copy and paste, bypassing the clipboard. eg:

Private Sub CommandButton1_Click()
Selection.Copy Sheets(2).Range("A1")
End Sub




"Libby" wrote:

Hi Guys,

I have a worksheet in which the cells are protected (userinterfaceonly)
which prevents the user from editing a cells contents.
However, for some cells, I would like them to be able to copy the contents
to the clipboard in order for them to work with it elsewhere.
Ive achieved this with a commandbutton which the user clicks to copy the
cell contents.

Code for commandbutton
Private Sub CommandButton1_Click()
Dim c As New DataObject
c.SetText Range("A" & ActiveCell.Row).Text
c.PutInClipboard
End Sub

The problem occurs if the cell content is quite long. It seems that there is
a limit to how much can be copied. Given that its possible to copy vast
quantities to the clipboard manually, I cant work out why this is.
To test this I took a blank sheet and in column A, rows 2 to 20 I placed
strings ranging in length from 20 to 2090 characters. I then added a button
and used it to copy the strings to the clipboard. I then pasted the clipboard
contents in an adjacent cell and compared the string lengths.
Ive discovered that the maximum number of characters that and be copied to
the clipboard is 1024. If you try and use the above code to copy a string
which is =1025 characters, when you paste the result it's only the first
1024!

Does anyone know why this is or how to get around it?

Many thanks in advance

Use .value, not .text.

..Text is the limiting factor.

c.SetText Range("A" & ActiveCell.Row).Value


Libby wrote:

Hi Guys,

I have a worksheet in which the cells are protected (userinterfaceonly)
which prevents the user from editing a cells contents.
However, for some cells, I would like them to be able to copy the contents
to the clipboard in order for them to work with it elsewhere.
Ive achieved this with a commandbutton which the user clicks to copy the
cell contents.

Code for commandbutton
Private Sub CommandButton1_Click()
Dim c As New DataObject
c.SetText Range("A" & ActiveCell.Row).Text
c.PutInClipboard
End Sub

The problem occurs if the cell content is quite long. It seems that there is
a limit to how much can be copied. Given that its possible to copy vast
quantities to the clipboard manually, I cant work out why this is.
To test this I took a blank sheet and in column A, rows 2 to 20 I placed
strings ranging in length from 20 to 2090 characters. I then added a button
and used it to copy the strings to the clipboard. I then pasted the clipboard
contents in an adjacent cell and compared the string lengths.
Ive discovered that the maximum number of characters that and be copied to
the clipboard is 1024. If you try and use the above code to copy a string
which is =1025 characters, when you paste the result it's only the first
1024!

Does anyone know why this is or how to get around it?

Many thanks in advance

--

Dave Peterson