Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
Hi,
I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
How about:
Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
Thanks GS!
Finally I've pasted something into a comment box!! Now another thing... do you know if I'm able to copy formats cos what Ive pasted is all jumbled etc. Almost need to be able to paste as a table?.... Is this pushing my luck!! Thanks, Mel "Gary''s Student" wrote: How about: Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
I am glad you are having success!
to get a better-look comment, just replace v = v & r.Value with v = v & Chr(10) & r.Value this will produce a single column in the comment. Making two columns in the comment is a little more tricky. -- Gary''s Student "Meltad" wrote: Thanks GS! Finally I've pasted something into a comment box!! Now another thing... do you know if I'm able to copy formats cos what Ive pasted is all jumbled etc. Almost need to be able to paste as a table?.... Is this pushing my luck!! Thanks, Mel "Gary''s Student" wrote: How about: Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
Thats better!
Just how complicated is 2 columns I wonder?..... Thanks, Mel "Gary''s Student" wrote: I am glad you are having success! to get a better-look comment, just replace v = v & r.Value with v = v & Chr(10) & r.Value this will produce a single column in the comment. Making two columns in the comment is a little more tricky. -- Gary''s Student "Meltad" wrote: Thanks GS! Finally I've pasted something into a comment box!! Now another thing... do you know if I'm able to copy formats cos what Ive pasted is all jumbled etc. Almost need to be able to paste as a table?.... Is this pushing my luck!! Thanks, Mel "Gary''s Student" wrote: How about: Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
Ooh, aswell, I forgot to add...
how hard would it be to NOT include zeros (or their corresponding code in the neighbouring cell)??? I've got data like this: Credits Debits 210 DS 363 92 999 OE 0 732 190 TS 0 0 and need 2 seperate comments (one for credits one for debits)... Putting in one column is lots better than not, but the list is quite long so to exclude zeros would be brill!! Thank you "Gary''s Student" wrote: I am glad you are having success! to get a better-look comment, just replace v = v & r.Value with v = v & Chr(10) & r.Value this will produce a single column in the comment. Making two columns in the comment is a little more tricky. -- Gary''s Student "Meltad" wrote: Thanks GS! Finally I've pasted something into a comment box!! Now another thing... do you know if I'm able to copy formats cos what Ive pasted is all jumbled etc. Almost need to be able to paste as a table?.... Is this pushing my luck!! Thanks, Mel "Gary''s Student" wrote: How about: Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
This version will produce two columns in the comment. Although the alignment
is not perfect is it legible. Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select Flip = 1 For Each r In Selection If r.Value = 0 Then w = " " Else w = r.Value End If If Flip 0 Then v = v & Chr(10) & w Else v = v & " " & w End If Flip = Flip * -1# Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub -- Gary''s Student "Meltad" wrote: Ooh, aswell, I forgot to add... how hard would it be to NOT include zeros (or their corresponding code in the neighbouring cell)??? I've got data like this: Credits Debits 210 DS 363 92 999 OE 0 732 190 TS 0 0 and need 2 seperate comments (one for credits one for debits)... Putting in one column is lots better than not, but the list is quite long so to exclude zeros would be brill!! Thank you "Gary''s Student" wrote: I am glad you are having success! to get a better-look comment, just replace v = v & r.Value with v = v & Chr(10) & r.Value this will produce a single column in the comment. Making two columns in the comment is a little more tricky. -- Gary''s Student "Meltad" wrote: Thanks GS! Finally I've pasted something into a comment box!! Now another thing... do you know if I'm able to copy formats cos what Ive pasted is all jumbled etc. Almost need to be able to paste as a table?.... Is this pushing my luck!! Thanks, Mel "Gary''s Student" wrote: How about: Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste clipboard to a comment
Cheers GS,
The alignment is more than fine for me! Looks great. I now need to do the same but for columns B and D - I tried to specify this in the range but the comments got messed up. Is there a way to select range B9:D50 excluding all column C??? Somebody gave me some differnt code on a seperate posting I made "Run time error '424' object required" and tries to merge aspects of the 2 codes but got stuck!! Help! :-) "Gary''s Student" wrote: This version will produce two columns in the comment. Although the alignment is not perfect is it legible. Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select Flip = 1 For Each r In Selection If r.Value = 0 Then w = " " Else w = r.Value End If If Flip 0 Then v = v & Chr(10) & w Else v = v & " " & w End If Flip = Flip * -1# Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub -- Gary''s Student "Meltad" wrote: Ooh, aswell, I forgot to add... how hard would it be to NOT include zeros (or their corresponding code in the neighbouring cell)??? I've got data like this: Credits Debits 210 DS 363 92 999 OE 0 732 190 TS 0 0 and need 2 seperate comments (one for credits one for debits)... Putting in one column is lots better than not, but the list is quite long so to exclude zeros would be brill!! Thank you "Gary''s Student" wrote: I am glad you are having success! to get a better-look comment, just replace v = v & r.Value with v = v & Chr(10) & r.Value this will produce a single column in the comment. Making two columns in the comment is a little more tricky. -- Gary''s Student "Meltad" wrote: Thanks GS! Finally I've pasted something into a comment box!! Now another thing... do you know if I'm able to copy formats cos what Ive pasted is all jumbled etc. Almost need to be able to paste as a table?.... Is this pushing my luck!! Thanks, Mel "Gary''s Student" wrote: How about: Sub meltad() Dim v As String Dim r As Range Sheets("KPI values").Select Range("B9:C50").Select For Each r In Selection v = v & r.Value Next Range("H11").AddComment Range("H11").Comment.Text Text:=v End Sub You can also concatenate CHR(10)'s into v to improve the appearance. -- Gary's Student "Meltad" wrote: Hi, I'm trying to copy a range in my worksheet then paste this to a single comment in a neighbouring cell. So far I've found this code for pasting from the clipboard to a comment (courtesey of Bob Greenblatt).... Sub InsertComment() Dim oComm As DataObject Set oComm = New DataObject With ActiveCell .AddComment oComm.GetFromClipboard .Comment.Text Text:=oComm.GetText(1) End With End Sub however I get an error (user type not defined) - so I must have left something out. Any ideas anyone?? I want to... Range("H11").AddComment "" Sheets("KPI values").Select Range("B9:C50").Select Selection.Copy then paste this into the comment box.... ??? THANKS, Mel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste from clipboard only | Excel Worksheet Functions | |||
clipboard copy & paste | Excel Discussion (Misc queries) | |||
Store clipboard before "vba do cut&paste" then recover stored information to clipboard? | Excel Programming | |||
a comment plugin & copy paste directly from excel to comment ? fr. | Excel Worksheet Functions | |||
Paste, clipboard | Excel Programming |