How to keep the cliboard, after executing a VBA code

[urielp]

Hello,

Before executing a VBA code, I copy a text from Excel or any other source into my clipboard, and I would like to keep using the clipboard after executing the macro.

For example. I have this simple code (to clear the data in filtered list), and I would like to keep using the cliboard after running the code. However I'm getting a gibberish after pasting the data:

Sub Show_All_Data() 'Without keeping the cliboard
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

Sub Show_All_Data() 'Keeping the cliboard after executing the code
On Error Resume Next
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
mystring = DataObj.GetText(1)
ActiveSheet.ShowAllData
With New MSForms.DataObject
.SetText mystring
.PutInClipboard
End With
Set objData = mystring
objData.SetText TempCopy2
objData.Text.PutInClipboard
End Sub

And this the text that I'm getting, after pasting the clipboard:
￿￿

What am I missing here?

Thanks

 

[Dan_W]

Hi. This seems like a similar problem experienced by another forum member. Check out my response here, and see if that helps: Post in thread 'Getting "??" when pasting from a TextBox' Getting "??" when pasting from a TextBox

 

[urielp]

Hi. This seems like a similar problem experienced by another forum member. Check out my response here, and see if that helps: Post in thread 'Getting "??" when pasting from a TextBox' Getting "??" when pasting from a TextBox

Hi,

I copied your 'cheap-and-nasty' VBa code, but I got an error message: Sub or Function not defined, related to 'SetClipBoardText' line. Why? How can I defne it?

Private Sub CommandButton2_Click()
SetClipBoardText TextBox5.Value

Dim GetMyText As String
GetMyText = GetClipBoardText
MsgBox GetMyText
End Sub

 

[Dan_W]

Hi,

I copied your 'cheap-and-nasty' VBa code, but I got an error message: Sub or Function not defined, related to 'SetClipBoardText' line. Why? How can I defne it?

Private Sub CommandButton2_Click()
SetClipBoardText TextBox5.Value

Dim GetMyText As String
GetMyText = GetClipBoardText
MsgBox GetMyText
End Sub

I would suggest you read the thread properly. The code you've copied has nothing to do with my code - that's the other person's original that I reworked to implement my solution.

Sub SetClipBoardText(ByVal Text As Variant)
    On Error Resume Next
    CreateObject("htmlfile").ParentWindow.ClipboardData.SetData "Text", Text
End Sub
Function GetClipBoardText() As String
    On Error Resume Next
    GetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("Text")
End Function
Sub ClearClipBoardText()
    On Error Resume Next
    CreateObject("htmlfile").ParentWindow.ClipboardData.clearData "Text"
End Sub

This is the code I proposed.