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