Excel VBA: Copy Cell value to clipboard

Discussion in 'Programming & Software Development' started by Plastik8, Jul 21, 2009.

  1. Plastik8

    Plastik8 Member

    Joined:
    Sep 8, 2003
    Messages:
    1,571
    Location:
    Adelaide
    Hi guys,

    I have a spreadsheet with a cell which I'd like the viewer to be able to copy to the clipboard by clicking a button. Here is the VBA script for the button;

    Private Sub CommandButton1_Click()

    Sheet1.Range("A1").Copy

    End Sub

    Now this works fine when there is simple content in the cell, for example a number or text. However, I would like cell A1 to have something like &A2&A3 in it.

    For example A2 = he, A3 = llo and therefore A1 = hello, but excel will not copy this to the clipboard via my VBA script.

    Is there a way to copy the contents of the cell via VBA? Perhaps I could make use of the indirect command? (I wasn't able to do so successfully).

    Thanks guys
     
  2. z2177199

    z2177199 Member

    Joined:
    Apr 19, 2002
    Messages:
    1,994
    Location:
    Sydney
    Test this code with
    A1 = &A2&A3
    A2 = he
    A3 = llo

    Private Sub test()
    Dim sarray() As String
    Dim i As Integer
    Dim scellID As String
    Dim scellIDValue As String
    Dim sConcatValue As String


    sarray = Split(Sheet1.Cells(1, 1), "&")

    For i = 1 To UBound(sarray)
    scellID = sarray(i)
    scellIDValue = Sheet1.Range(scellID)
    'Debug.Print i & " " & scellID & " " & scellIDValue
    sConcatValue = sConcatValue & scellIDValue
    Next i
    'Debug.Print sConcatValue

    Sheet1.Range("b1") = sConcatValue
    Sheet1.Range("b1").Copy

    End Sub
     
  3. bugayev

    bugayev Whammy!

    Joined:
    May 15, 2003
    Messages:
    4,096
    Location:
    Melbourne
    Have you just tried getting the value of the cell using .value?
     
  4. OP
    OP
    Plastik8

    Plastik8 Member

    Joined:
    Sep 8, 2003
    Messages:
    1,571
    Location:
    Adelaide
    Thanks for the replies guys! I'll look over the code and study it. I'm very new to VBA.

    Turns out the problem was that the cell was hidden. Is there a way to copy from a cell that is hidden, other than first making it visible and then making it hidden again in a script? seems a bit messy.
     

Share This Page