Macro to create a comment with contents from Paste

[Steve Walton]

I want to be able to create a comment for the current cell and add the
text currently in the Paste buffer.

The macro works fine, but hard codes the text into the macro

Range("E2729").Comment.Text Text:= _
"SteveW:" & Chr(10) & "hard code text....!"

How can I get it to insert paste buffer ?

 

[Guest]

Cheat and use a "helper" cell:

1. Enter the macro below
2. ASSIGN A SHORTCUT KEY
3. Select the cell you want to copy and do the copy
4. Select the destination cell and run the macro VIA THE SHORTCUT

The macro will do a paste into the helper cell (Z100) and then establish a
comment in the destination cell using the contents of the helper cell.


I stress using the shortcut key because using the menu bar to activate the
macro will ruin the copy/paste buffer (clipboard)


Sub cheater()
Dim r As Range
' gsnu
Set r = Selection
Range("Z100").Select
ActiveSheet.Paste
r.AddComment
r.Comment.Visible = False
r.Comment.Text Text:=Range("Z100").Value
r.Select
End Sub

 

[Guest]

The things one learns about from the Excel community! I'd never really
looked into the clipboard - I found this link under another thread:
'http://www.word.mvps.org/FAQs/MacrosVBA/ManipulateClipboard.htm.
Just got to remember to tick M/soft Forms Library in Tools, References (or
make a userform and then remove it).
Didn't know Excel could speak either!

Sub PasteIntoComment()
Dim MyData As DataObject
Dim strClip As String
Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
Range("E2729").AddComment "SteveW:" & Chr(10) & MyData.GetText
Application.Speech.Speak MyData.GetText
End Sub

 

[Steve Walton]

Cheers Gary, that'll work and does
Only query, if paste buffer is multiple line text, guess this will not
all go into Z100, suppose I can loop and test cells below it etc

BTW for me a Macro button worded, in fact Run Macro from menu seemed
ok as well. Neither lost the paste contents.

Steve

 

[Guest]

Good work Martin! I didn't realize the clipboard could be easily manipulated.

 

[Steve Walton]

What version of Excel is that ?
I have 2000

The things one learns about from the Excel community! I'd never really
looked into the clipboard - I found this link under another thread:
'http://www.word.mvps.org/FAQs/MacrosVBA/ManipulateClipboard.htm.
Just got to remember to tick M/soft Forms Library in Tools, References (or
make a userform and then remove it).
Didn't know Excel could speak either!

Sub PasteIntoComment()
Dim MyData As DataObject
Dim strClip As String
Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
Range("E2729").AddComment "SteveW:" & Chr(10) & MyData.GetText
Application.Speech.Speak MyData.GetText
End Sub

 

[Guest]

Sorry Steve, I was getting carried away with the speaking thing which only
works in 2003 (handy for the blind user). Just cut "Application.Speech.Speak
MyData.GetText" and the rest should work OK in 2000.