Hi, Need some help - Copy pasting and creating coments! Thanks

[Closed]
Report
Posts
4
Registration date
Tuesday January 15, 2013
Status
Member
Last seen
January 16, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

Getting a little stuck - would anyone know how to copy text/number from one cell and with one function: in a new cell create a comment AND paste the copied info directly into the comment?

Would really appreciate some help on this!

Thanks !



5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you can try some thing like this

    With Cells(1, "A")
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:="" & Cells(1, "B")
    End With
Posts
4
Registration date
Tuesday January 15, 2013
Status
Member
Last seen
January 16, 2013

Hi,

Firstly thank you Rizvisa1, really appreciate the quick response.


Sorry, im slightly new at excel -

1. click on view - macros
2. record macro
3. put in a short cut key (i put "j")
4. pase what you sent me :

With Cells(1, "A")
.AddComment
.Comment.Visible = False
.Comment.Text Text:="" & Cells(1, "B")
End With

---- in the discription

5. click on 'ok'

6. Then i wrote something (xyz) . pressed control C, went to a new cell, and pressed "cntrl j"

-- however as a result my excel crashed and it closed the program..


Anything I am doing wrong?

Thanks a bunch for your time!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
cannot say why it crashed, but do this

1. write some text in cell b1
2. press alt +f11 at same time
3. inset new module
4. paste code

    With Cells(1, "A")
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:="" & Cells(1, "B")
    End With


4. run macro
5. check to comments in cell a1
Posts
4
Registration date
Tuesday January 15, 2013
Status
Member
Last seen
January 16, 2013

Hey,

it says compile error, invalid outside procedure.
and highlights the first part of the macro

Not sure what to do next. Appreciate the help!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
oh. you need to add sub

Public Sub insertComment()
    With Cells(1, "A")
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:="" & Cells(1, "B")
    End With
End Sub
Posts
4
Registration date
Tuesday January 15, 2013
Status
Member
Last seen
January 16, 2013

Hey,

The macro essentially creates a blank comment in the cell A1 text.

What i needed was a faster way of doing the follwing:


copy stuff from one cell - go to another cell - insert a comment box in that new cell - paste what i copied from the first cell

a one way faster way of doing the above.

Thanks so much for your help!
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Shock,

See if this code satisfies your needs:
Sub test()
Dim x As String

x = InputBox("Please input cell you would like to copy, (as in A1).")

With ActiveCell
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:=Range(x).Value
    End With

End Sub

1. Select the cell you would like to add the comment to.
2. Run the code (you already know how to add a shortcut key to the code.).
3. An inputbox appears asking you which cell you would like to copy the contents from.

Best regards,
Trowa