Macro to create a comment with contents from Paste

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 ?


--

Steve

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
--
Gary's Student


"Steve Walton" wrote:

> 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 ?
>
>
> --
>
> Steve
>

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" wrote:

> 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 ?
>
>
> --
>
> Steve
>

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


On Tue, 7 Mar 2006 08:08:32 -0800, Gary''s Student
<[email protected]> wrote:

>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

--

Steve

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


"Martin" wrote:

> 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" wrote:
>
> > 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 ?
> >
> >
> > --
> >
> > Steve
> >

What version of Excel is that ?
I have 2000


On Tue, 7 Mar 2006 08:17:26 -0800, Martin
<[email protected]> wrote:

>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" wrote:
>
>> 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 ?
>>
>>
>> --
>>
>> Steve
>>

--

Steve

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.

"Steve Walton" wrote:

> What version of Excel is that ?
> I have 2000
>
>
> On Tue, 7 Mar 2006 08:17:26 -0800, Martin
> <[email protected]> wrote:
>
> >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" wrote:
> >
> >> 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 ?
> >>
> >>
> >> --
> >>
> >> Steve
> >>
>
> --
>
> Steve
>