Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Clipboard as Text (2000 - 2003)

    In Excel Visual Basic how do I put characters onto the clipboard (the equivalent of Select Cell, press F2 and use arrows while holding Shift key, then <Ctrl>C)? Having done so, how do I access those characters, for example to return them as the the result of a Visual Basic function? And how do I set such a character string into a cell?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clipboard as Text (2000 - 2003)

    If you are in visual basic, and you want to get the third through the tenth characters from cell A1 and put it into a string variable and put that string into cell B1, then you would do something like this:

    <pre>Dim str3Thru10 As String
    str3Thru10 = Mid(Worksheets("Sheet1").Range("A1").Value, 3, 8)
    Worksheets("Sheet1").Range("B1").Value = str3Thru10
    </pre>


    If you want to return the third througn the 10th characters in the cell passed to a user defined function you would do something like this:

    <pre>Public Function Ret3Thru10(oCell As Range) As String
    Ret3Thru10 = Mid(oCell.Value, 3, 8)
    End Function
    </pre>


    You do not need to put anything onto the clipboard to do either of those.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Clipboard as Text (2000 - 2003)

    Dear Legare,

    Thank you for your help. I think I have omitted some information that may be important. At times I need to export or import these character strings to/from other applications and the best way I know to do this with flexibility is via the clipboard. Being on the clipboard as text gives the clipboard greater longevity and resistance to Excel events. A lot of the work I do is with legacy Macros 4 language (hence "Antediluvian"), and I also find difficulty in moving text from a single cell to a single but merged cell, even using "FORMULA". Thus my necessary forays into VB. Thanks again for your assistance.

    regards

    Geoffrey

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clipboard as Text (2000 - 2003)

    Activate the Visual Basic Editor.
    Select Insert | Userform.
    Remove the userform again (right-click it in the Project Explorer and select Remove UserFormn)
    This seemingly useless series of actions has set a reference to the Microsoft Forms 2.0 Library. This library contains an object DataObject and methods GetText, SetText, GetFromClipboard and PutInClipboard to manipulate the clipboard. Type any of these words in the Visual Basic Editor and press F1 to get help, with examples of how to use them.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clipboard as Text (2000 - 2003)

    I'm not the best with VBA code, but with the advice of Legare and Hans, I put this together:
    It uses the Function to collect the charater string you require, and copies it to the clipboard, sothat it can be pasted into excel or any other windows based application.
    <pre>Option Explicit

    Option Explicit

    Sub CopyCharsToClipboard()
    Dim MyData As DataObject
    Set MyData = New DataObject
    Application.Dialogs(xlDialogFunctionWizard).Show
    MyData.SetText ActiveCell.Value
    MyData.PutInClipboard
    ActiveCell.Value = ""
    End Sub
    Public Function Copy_Chars(Original_String As Range, _
    Start_Char As Integer, _
    Num_Chars As Integer) As String
    Copy_Chars = Mid(Original_String.Value, Start_Char, Num_Chars)
    End Function

    </pre>

    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clipboard as Text (2000 - 2003)

    Thanks for posting an example of how to use SetText and PutInClipboard, but what is the purpose of showing the Function Wizard? And where do you use the Copy_Chars function?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clipboard as Text (2000 - 2003)

    <hr>1. What is the purpose of showing the Function Wizard?
    2. And where do you use the Copy_Chars function?<hr>
    1. It would have been nice to have the CopyCharsToClipboard macro activate the Copy_Chars Function directly and not have to activate the Function Wizard that the user needs to select the Copy_Chars function. However, if it is the case that you have to go through the function wizard to activate the palette, the user will trigger, the CopyCharsToClipboard macro, and select the Function under the User Defined category.
    2. When the palette opens the user specifies the cell containing the text, and the start and number of characters needed. The rest of the macro then assigns the characters to the clipboard.

    Is there a better way of doing this, i.e. : Collecting the start and number of chars with input boxes, and use the values internally in code??? Its not so clean!
    I will appreciate some advice!
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clipboard as Text (2000 - 2003)

    We'd better wait to see what Antediluvian thinks of this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •