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

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
>

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

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

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

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

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

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