A
atpgroups
I am trying to swap the contents and formats of two equally sized
ranges. (this is part of a specialised sorting routine).
The obvious way seems to be to use a DataObject to hold the formatted
text while doing the switch, but this appears to be a total failure.
Here is the code, try running it with two different words in cells A1
and B1
Sub test()
Dim A As New MSForms.DataObject
Sheet1.Range("A1").Cut
A.GetFromClipboard
Debug.Print "1 " & A.GetText
Sheet1.Range("A2").Copy Sheet1.Range("A1")
Debug.Print "2 " & A.GetText
B.PutInClipboard
Sheet1.Range("A2").PasteSpecial
End Sub
See how the Copy line alters the text property of A? Surely that isn't
right.
To compound matters, the PasteSpecial line crashes my Excel (2003) by
creating a blank text frame, and disabling all the menus....
Perhaps the DataObject is the wrong class to hold formatted range
data? Any suggestions how to swap two ranges while keeping the
formatting? Currently I am thinking of creating a temporary worksheet
and hiding it, but that seems very untidy.
ranges. (this is part of a specialised sorting routine).
The obvious way seems to be to use a DataObject to hold the formatted
text while doing the switch, but this appears to be a total failure.
Here is the code, try running it with two different words in cells A1
and B1
Sub test()
Dim A As New MSForms.DataObject
Sheet1.Range("A1").Cut
A.GetFromClipboard
Debug.Print "1 " & A.GetText
Sheet1.Range("A2").Copy Sheet1.Range("A1")
Debug.Print "2 " & A.GetText
B.PutInClipboard
Sheet1.Range("A2").PasteSpecial
End Sub
See how the Copy line alters the text property of A? Surely that isn't
right.
To compound matters, the PasteSpecial line crashes my Excel (2003) by
creating a blank text frame, and disabling all the menus....
Perhaps the DataObject is the wrong class to hold formatted range
data? Any suggestions how to swap two ranges while keeping the
formatting? Currently I am thinking of creating a temporary worksheet
and hiding it, but that seems very untidy.