Copy Cell Content on Click

[excelnow]

Hi,

I am not sure if this is possible because I couldn't find anything about this on the web and on this forum.

I want to copy the cell content(text) when I click on it. Can this be done via code?

Thanks

 

[Fucius]

hrmmmmm...... okay, assuming what I pasted before was correct this is what I tried with your suggestion:


Sub Singleclick(ByVal Target As Range) 'single click version

If Intersect(Target, Range("A1:J281")) Is Nothing Then Exit Sub

Dim DataObj As New MSForms.DataObject
Dim S As String
S = "ActiveCell.Text"
DataObj.SetText S
DataObj.PutInClipboard

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
Debug.Print S

End Sub


Still not working unfortunately. This is so frustrating, I'm positive there has to be a way because I'm able to copy and paste text directly from notepad so if it works on notepad, why isn't it working for excel. grrrrrrr.....

Thanks again btw Mr Excel, if you have any other suggestions please feel free to share.

 

[VoG]

Delete the ""

 

[Fucius]

hrmmm..... okay, newbie question. what I'm doing is opening up the Visual Basic editor/view code/paste code into code field/clicking run but every time I do that it opens up a macro window asking me to name and create a new one. When I click create, it adds the following code to end of what I pasted:

Sub workbook()

End Sub

-The script still runs though, does this make a difference?

I ran the following inspite of the above and got the same results:

Sub test(ByVal Target As Range) 'single click version

If Intersect(Target, Range("A1:J281")) Is Nothing Then Exit Sub

Dim DataObj As New MSForms.DataObject
Dim S As String
S = ActiveCell.Text
DataObj.SetText S
DataObj.PutInClipboard

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
Debug.Print S

End Sub

 

[VoG]

It should be

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'
'your code here
'
End Sub

which has to go in the ThisWorkbook module.

 

[Fucius]

It should be

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'
'your code here
'
End Sub

which has to go in the ThisWorkbook module.

hrmmmm..... I seem to now be getting the following error:

"Compile error:

Procedure declaration does not match description of event or procedure having the same name."

I tried googling this and they say it could possibly be a windows 7 thing? argh, so frustrating.

http://social.msdn.microsoft.com/Fo...s/thread/3a4ce946-effa-4f77-98a6-34f11c6b5a13

 

[VoG]

Where did you put the code? Press ALT + F11 to open the Visual Basic Editor, in the Project window double click ThisWorkbook then paste in the code.

 

[Fucius]

ah okay, got it but now I'm getting this:

"Compile error: User-defined type not defined"

 

[Fucius]

in the project window I double clicked "ThisWorkbook" and pasted the following:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("A1:J281")) Is Nothing Then Exit Sub

Dim DataObj As New MSForms.DataObject
Dim S As String
S = ActiveCell.Text
DataObj.SetText S
DataObj.PutInClipboard

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
Debug.Print S

End Sub

End Sub

 

[Fucius]

The following line is highlighted in yellow with an arrow pointing to it on the far left side:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

 

[VoG]

Have you set a reference to the Microsoft Forms 2.,0 Library? See http://www.cpearson.com/excel/clipboard.aspx