cell comment

[Jack Sons]

Hi all,

In cell B6 I have a formula like this:

="sometext "&G6-H6&CHAR(10)& " state = " & round(AB6;2) & " (until date "
& TEXT(TODAY(); "d-m-yyyy") & ") " & CHAR(10) & " balance =
"&Q6

This results in three lines of text that I want to put in the cell comment
of N6 (that may or may not already have a cell comment that could have text
or nothing in it).

I tried a lot with code like

Range(...).ClearComments
Range(...).AddComment
Range(...).Comment.Visible = False
Range(...).Comment.Text Text:=Range(Cells(6, 2), Cells(6,2 )).Value
Comment.Shape.TextFrame.AutoSize = True

but that apparently is not the right way.

What is the proper code to accomplish my goal?

TIA

Jack Sons
The Netherlands

 

[Dave Peterson]

I modified your code slightly and it worked ok for me:

Option Explicit
Sub testme()

With ActiveSheet.Range("N6")
.ClearComments
.AddComment Text:=.Parent.Range("B6").Value
.Comment.Visible = False
.Comment.Shape.TextFrame.AutoSize = True
End With

End Sub

 

[Jack Sons]

Dave,

Thanks, it works nicely.

Ten minutes before I saw your answer I discovered that my code also works if
I only change the word "Value" in "Text".
Do you think it will make a difference in the execution of the code?

I tried to let this work with the parent on another sheet, see my code
below. It won't work. What is wrong?

When I leave out the "Sheets("Blad2")." it works like charm.
Must parent be on the same worksheet? If not, can it also be on another
workbook?


Sub testme()
rij = 1
Do Until rij = 651
With Sheets("Blad1").Range(Cells(rij, 1), Cells(rij, 1))
.ClearComments
.AddComment Text:=.Parent.Sheets("Blad2").Range(Cells(rij, 11),
Cells(rij, 11)).Value
.Comment.Visible = False
.Comment.Shape.TextFrame.AutoSize = True
End With
rij = rij + 1
If rij Mod 50 = 0 Then Beep
Loop

End Sub

Jack.

 

[Dave Peterson]

When you write this:

With Sheets("Blad1").Range(Cells(rij, 1), Cells(rij, 1))

Then everything that comes later with a space_dot (.clearcomments) refers to
that with object.

And in your case, that object is a single cell.

That single cell lives on a worksheet--it's parent.

So .Parent.Sheets("Blad2") says to go up to the worksheet("blad2"), then go to
the sheet ("Blad2") that's part of sheets("blad1") and there aint no such beast!

You could try something like:

Sub testme()
rij = 1
Do Until rij = 651
With Sheets("Blad1").Cells(rij, 1)
.ClearComments
.AddComment Text:=.parent.Parent.Sheets("Blad2").Cells(rij, 11).Value
.Comment.Visible = False
.Comment.Shape.TextFrame.AutoSize = True
End With
rij = rij + 1
If rij Mod 50 = 0 Then Beep
Loop

End Sub

The .parent.Parent.Sheets("Blad2")... line, says go up to the parent of the cell
(Blad1), then up to the parent of the worksheet (whatever workbook holds this
code), then down to sheets("blad2").

You can follow the parent up as far as the family tree allows--you'll end up at
the excel application in just a little bit.

And since you're only using one cell, I used .cells() instead of
..range(.cells(),.cells())

Since you're running this against the activeworkbook, you could have also used:

..AddComment Text:=Sheets("Blad2").Cells(rij, 11).Value

 

[Jack Sons]

Dave,

Your explanation is very helpful. I allready tried to find the secrets of
"parent" in the help, but it said very little about it. I'll try out the new
code later, hope I got it now.

Jack.

 

[Dave Peterson]

Run a few tests..
dim myRng as range
set myrng = range("a1")
msgbox myrng.parent.name
msgbox myrng.parent.parent.name
msgbox myrng.parent.parent.parent.name

until it blows up real good!

Dave,

Your explanation is very helpful. I allready tried to find the secrets of
"parent" in the help, but it said very little about it. I'll try out the new
code later, hope I got it now.

Jack.