How to stop clipboard being cleared by VBA code?

[sarujin]

All,

I have been developing an excel workbook menu system for my company over the last few months, and currently have encountered an issue regarding the clipboard with copying and pasting.

On every sheet under

Private Sub Worksheet_Activate

there is some code for generating of that sheets top 3 rows (in these rows there is some hyperlink creation, text resizing etc.

My problem is, if I am on one sheet and I select and copy any data/values, when I click over to a different sheet/tab, the various code in VBA clears the clipboard. Rendering me unable to paste what I have just copied on the prior sheet.

This appears due to the selection changeing, as the visual basic code is run: Currently code like this is enough to 'grey' out the paste button in the excel ribbon.

Cells(1, 1).Font.Bold = True

Is there anyway around this? maybe even setting the clipboard value at the begining of activating a new worksheet to a variable, and then retrieving on the final line of the vba code?

Regards.

 

[westconn1]

you can use a dataobject

Dim d As DataObject
Set d = New DataObject
d.GetFromClipboard
myclip = d.GetText  ' myclip variable would now contain any text from clipboard

you may need reference to forms2 object library

 

[adelkam]

sarujin ,
Have you resolved this problem?
I have exactly the same thing!!

 

[AlphaFrog]

sarujin ,
Have you resolved this problem?
I have exactly the same thing!!

It looks like sarujin hasn't been around for about six years.

Perhaps you could paste before doing anything else that would clear the clipboard.

 

[adelkam]

It looks like sarujin hasn't been around for about six years.

Perhaps you could paste before doing anything else that would clear the clipboard.

Thanks AlphaFrog,

But how can I accomplish this? .. This example summarizes what drives me crazy!:
Sheet1: contains some data
Sheet2: is blank worksheet, but contains this line of code in the initiate event:

Private Sub Worksheet_Activate()    
ActiveSheet.Range("F6:S6").Orientation = 90
End Sub

When I copy random rows from sheet1, then I click on sheet2 to paste it, the paste button gets grayed out. It appears that the clip board gets cleared if the target worksheet contains any code for formatting cells. Is it a glitch in MS Excel. How can I accomplish copy & paste under these circumstances? .. Thanks

 

[AlphaFrog]

Thanks AlphaFrog,

But how can I accomplish this? .. This example summarizes what drives me crazy!:
Sheet1: contains some data
Sheet2: is blank worksheet, but contains this line of code in the initiate event:

Private Sub Worksheet_Activate()    
ActiveSheet.Range("F6:S6").Orientation = 90
End Sub

When I copy random rows from sheet1, then I click on sheet2 to paste it, the paste button gets grayed out. It appears that the clip board gets cleared if the target worksheet contains any code for formatting cells. Is it a glitch in MS Excel. How can I accomplish copy & paste under these circumstances? .. Thanks

Can I ask why you want to change the display orientation of that range each time the sheet is activated? Is it necessary to do it every time the sheet is activated? Perhaps there is another way depending on why you need it done.