Indeed DataObject is useful for some purposes but need to bear in mind it
only stores a single plain text string (could include say tabs & linebreaks
to re-construct an array). Even with that limitation it still requires
knowledge of what should be in the clipboard.

Rightly, or possibly wrongly, I understood the OP did not know what (cell
range) was in the clipboard. Otherwise it would be a simple matter to
re-copy the same range after inadvertently clearing the clipboard.

Regards,
Peter T


"dbKemp" wrote in message
...
On Jan 5, 10:47 am, John B wrote:
Thanks.. That's what I was beginning to suspect! Is there a way to pull
the
clipboard data out as an object, do this operation, then pop it back in?

"Peter T" wrote:
Unfortunately whenever you change the interface the clipboard is
cleared, or
rather Excel's clipboard that in effect refers to the cell range is
cleared.

Regards,
Peter T

"John B" <John wrote in message
...
I have a VBA Sub that runs on a change of worksheet within my excel
application.

If I include either

With ActiveWindow
.DisplayGridlines = blnShow
.DisplayHeadings = blnShow
.DisplayHorizontalScrollBar = blnShow
End With

Or

Application.DisplayFormulaBar = blnShow

(where blnShow is a boolean value)
in this function, then I seem to lose the ability to paste
information
that
I have copied before moving worksheet. I can see the information in
the
clipboard history. but I cannot paste it, either into excel or into
notepad.
I'm not sure if the clipboard is being cleared, or whether it's just
some
link to the clipboard.

Setting other Application properties also cause this behaviour, but
not
Application.ScreenUpdating = True

Has anyone seen this behaviour before and/or do they know how to stop
it?

I was working on the same problem yesterday. Combining info from this
thread and another one from this forum I came up with a way to restore
the clipboard. It uses the DataObject object (requires reference to
MS Forms Object Library. I created a class to handle everything so I
didn't clutter the original function.

First I put the clipboard contents to the DataObject and then saved it
as a string sBefore. Then did the operations that cleared the
clipboard. After this I put the contents of the clipboard into the
DataObject and saved it to another string sAfter. I then did some
logic to test both strings in order to avoid errors if nothing was in
clipboard, or if clipboard contained info from an app other than
Excel. I put the DataObect contents back into the clipboard if I
determined that it was from Excel.

If len(sAfter) was not 0, then no action was taken.
if len(sAfter) was 0, and len(sBefore) 0 then sBefore was loaded into
the DataObject and then the clipboard was restored from the
DataObject. Excel 2003 help explains all the methods required.