Overclockers Australia Forums
OCAU News - Wiki - QuickLinks - Pix - Sponsors  

Go Back   Overclockers Australia Forums > Software Topics > Programming & Software Development

Notices


Sign up for a free OCAU account and this ad will go away!
Search our forums with Google:
Reply
 
Thread Tools
Old 21st July 2009, 1:38 PM   #1
Plastik8 Thread Starter
Member
 
Plastik8's Avatar
 
Join Date: Sep 2003
Location: Adelaide
Posts: 1,559
Default Excel VBA: Copy Cell value to clipboard

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
__________________
HTPC: | XBMC on Win 7 | 4GB DDR2 | | 6TB ∑HDD | Toslink >> Yamaha Receiver >> 5.1 | ATI 5450 (Passive) >> HDMI >> Samsung LA46A650 |
Game PC: | Intel i5 750 | 8GB DDR3 | AMD 7850 | Win 8 64Bit |
Plastik8 is offline   Reply With Quote

Join OCAU to remove this ad!
Old 21st July 2009, 1:57 PM   #2
z2177199
Member
 
Join Date: Apr 2002
Location: Sydney
Posts: 1,994
Default

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
__________________
F@H3 - 14/06/2002
z2177199 is offline   Reply With Quote
Old 21st July 2009, 2:15 PM   #3
bugayev
Whammy!
 
bugayev's Avatar
 
Join Date: May 2003
Location: Melbourne
Posts: 3,983
Default

Quote:
Originally Posted by Plastik8 View Post
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
Have you just tried getting the value of the cell using .value?
__________________
Administrator: Apple Desktop Hardware/Software, Graphics and Programming, Digital Art, iOS Devices, For Sale (PC and Non-PC), Price Check and What/Where Should I Buy?
mercury: 11" Macbook Air, i5, 4Gb RAM, 128Gb SSD
lunchbox: Mac mini, i5 Dual Core, 4Gb RAM, 500Gb HDD
bugayev is offline   Reply With Quote
Old 21st July 2009, 2:17 PM   #4
Plastik8 Thread Starter
Member
 
Plastik8's Avatar
 
Join Date: Sep 2003
Location: Adelaide
Posts: 1,559
Default

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.
__________________
HTPC: | XBMC on Win 7 | 4GB DDR2 | | 6TB ∑HDD | Toslink >> Yamaha Receiver >> 5.1 | ATI 5450 (Passive) >> HDMI >> Samsung LA46A650 |
Game PC: | Intel i5 750 | 8GB DDR3 | AMD 7850 | Win 8 64Bit |
Plastik8 is offline   Reply With Quote
Reply

Bookmarks

Sign up for a free OCAU account and this ad will go away!

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +10. The time now is 6:15 PM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd. -
OCAU is not responsible for the content of individual messages posted by others.
Other content copyright Overclockers Australia.
OCAU is hosted by Internode!