VBA script causes clipboard to be lost

[John B]

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?

 

[Peter T]

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]

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]

You mean something like identify the cell_ref that's in the clipboard,
"accidentally" clear the clipboard, then recopy the previously identified
cell range.

I'm almost sure (done it before) that should be possible but after spending
a little time messing around with clipboard API's, identifying the available
clipboard formats, extracting the data associated with the respective
formats I can't find anything to return the cell-ref.

FWIW a large range of clipboard data can be extracted, including the size of
the copy range, contents of course. Also a surprising amount of other
information about the worksheet, all the custom number formats, even a full
RGB definition of the workbook's 56 colour palette and more - but not the
cell_ref!

Regards,
Peter T

 

[Kenneth Hobson]

Did you try this after a copy?
Application.CutCopyMode=False

Two other concepts could be used to work around the problem. Both use a
scratchpad method.
1. Copy to and paste from the a UserForm's Spreadsheet control.
2. Copy to and paste from a scratch xls.

I have used method (1) to hold multiple copies as plain text in Userform
Textbox controls.

 

[dbKemp]

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?

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.

 

[Peter T]

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