Creating single comment from a clipboard content

[ssurjie]

Hi I am Sam, I used a lot of Excel vb scripts to speedup my productivity at work. I come here a lot. Today will make three weeks
i am looking for a macro that could create a single comment from the clipboard comments taken from another program.

[ssurjie]

I found this...
Sub test()
Dim String1 As String
Dim String2 As String
String1 = Cells(1, 1).Value
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(1, 1), Cells(1, 1))
String2 = Cells(1, 1).Value
Cells(1, 1).Value = String1
[ActiveCell].AddComment Text:=String2
End Sub

However the comment box is kind fixed to one size and knocks out some text.

[xLJer]

There may be something in this code you can use.
The clipboard is copied to cell "IV1", then added to a new
comment in the cell selected when the macro is run.
The macro exits if the clipboard is empty.
The comment box is auto-sized and cell "IV1" is cleared of the text.

Please Login or Register  to view this content.

[ssurjie]

Thanks for your response, I tried it with a 5 lines paragraph copied to the clipboard...
but it is only displaying the first line like the one I posted earlier.

[xLJer]

Try the attached Excel 2003 macro and see if the sample text strings work for you.
Also test it with your own text. The comment box width and height are set by the code.
You can adjust the width by changing the value of the variable cWidth.
Ctrl+e has been assigned to run the macro.
Revised code:

Please Login or Register  to view this content.

[ssurjie]

It works great when all the texts fits in a single row in a spreadsheet or notepad.
But if you have sentences under each other, it lists only the first line as before.
But this is the best macro so far, it probably needs some tweaking with the formatting only,
everything else seems to cooperate. Thanks again

[ssurjie]

Could you squeeze this code prior to execute your code above, as this one grabs
multiple rows of texts and convert them
Sub column2row()
'it works but put everything on one row
Dim cl As Range, Txt As String
For Each cl In Selection
If cl <> "" Then
Txt = Txt & cl & Chr(32)
End If
Next cl
Range("A12") = Txt
End Sub

[JosephP]

please add code tags to all your posts in accordance with rule #3 before we proceed any further

[ssurjie]

I found two other that could convert displayed cells data to comments

It works !!! It copy multiple cells to a single comment and list vertically

Sub cmt_from_multiplecells_single_column()
With Range("A2")
.NoteText Text:=Join(Application.Transpose(Range("A3:A7").Value), vbLf)
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub


It works !!! It copy multiple cells to a single comment and list vertically
Sub Comment_specific_cells_edited()

Dim vData: vData = Application.Transpose(Range("A3:A7").Value)

With Range("A2")

.AddComment Text:=Join(vData, vbLf)

End With

End Sub

[ssurjie]

I apologise for the missing code tags you mentioned above..apology

[JosephP]

you can still add them ;-)

(it is possible to get the text straight from the clipboard without pasting to cells)

[xLJer]

If what you are trying to do is display one or more cells of text
in a comment box, and have each cell's text start on a new line,
then replace the code in my workbook with the code below.
Copy the code from here, right-click on Sheet1 tab, choose View Code,
replace the sheet module code with this code:

Please Login or Register  to view this content.

Select one or more cells that have text and press Ctrl+e to run the macro.

[JosephP]

please do not continue this until my request is complied with

[ssurjie]

Perfect ! This stands out by itself.
One more tiny tweak is all I need now for my end
I will be actually copying the texts from another program.
Then I will use this one to continue
Could you change this line from A1 to any activecell ?
commentedCell = "A1"
as I would want my comments to appeared in random cells
Thanks a lot

[JosephP]

for the last time please add code tags to your posts