0

what I am trying to achieve is my work has cell data that is defined in the cell and the description is stored in a comment. is it possible to remove and store this into a string or something similar to allow me to paste it into a printable format, on a generated printout page.

dim myval as string
dim cmt as comment

Sheets("DData").Activate
set cmt = [H4].comment
myval = cmt.comment
Sheets("Generated Profile").Activate
cells(24,7) = myval

This is my form: Picture of Form

And this is my workbook data page: Picture of my workbook data page

any help would be greatly appreciated, this is my first post here so if am improperly posting please redirect me.

thanks.

Zackd41pro
  • 11
  • 1
  • 5
  • Looks like it is in `myval`, which is a string. Can you explain where the problem is exactly? – cybernetic.nomad Nov 29 '18 at 19:53
  • thanks for the response, what happens when i reach the line where myval is trying to grab cmt.text. i actually just found the solution i was calling cmt.comment not cmt.text when writing to myval. – Zackd41pro Nov 29 '18 at 19:59
  • A lot going on between...`Sheets("Generated Profile").Cells(24,7) = Sheets("DData").Cells(4,8).Comment.Text`. No need to Activate twice or set intermediary values. – Darrell H Nov 29 '18 at 20:02
  • Darrell H, thanks didn't know that was a viable option pulling specifically without changing the page. – Zackd41pro Nov 29 '18 at 20:06
  • In general it's a good idea to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Nov 29 '18 at 20:46

2 Answers2

0

Try something like this:

Dim comment As String
comment = Range("A1").comment.Text

Replacing the cell above with whatever cell you want.

Brad
  • 272
  • 2
  • 7
  • 22
0

I figured out that i fat fingered the entry of the code when

myval = cmt.comment

should be 

myval = cmt.text

although it should be mentioned that Darrell h (quoted below gave a better solution) here is their code.

Sheets("Generated Profile").Cells(24,7) = Sheets("DData").Cells(4,8).Comment.Text

"A lot going on between...Sheets("Generated Profile").Cells(24,7) = Sheets("DData").Cells(4,8).Comment.Text. No need to Activate twice or set intermediary values." – Darrell H

Zackd41pro
  • 11
  • 1
  • 5