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

Macro to create a comment with contents from Paste
 

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

Macro to create a comment with contents from Paste
 

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

Macro to create a comment with contents from Paste
 

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

Macro to create a comment with contents from Paste
 

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

Macro to create a comment with contents from Paste
 

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

Macro to create a comment with contents from Paste
 

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