Changing the Default Position of a Dynamic Comment as it overlaps the Cell.

[e4excel]

Changing the Default Position of a Dynamic Comment as it overlaps the Cell.

Dear Forum,

I am dynamically making a comment with VBA which unfortunately is overlapping the CELL of which its a comment..

I want it to be more on the RIGHT of the cell as thats the last filled column with plenty of Open Space on the RIGHT..

With Sheets(NewSheetName)
    
    Comm = Format(Range("H4").Value, Strdate) & Chr(10) & "Opening Balance : " & Chr(10) & _
    Application.WorksheetFunction.Text(.Cells(4, 7), "[>9999999]""Rs ""#\,##\,##\,##0.00;[>99999]""Rs ""#\,##\,##0.00;""Rs ""#,##0.00")
    
  
    .Cells(8, 10).AddComment Text:=Comm
    .Cells(8, 10).Comment.Visible = True
    .Cells(8, 10).Comment.Shape.TextFrame.AutoSize = True
           
    End With
 
    
    With Sheets(NewSheetName).Cells(8, 10).Comment.Shape.TextFrame.Characters.Font
      .Name = "Times New Roman"
      .Size = 11
      .Bold = True
      .ColorIndex = 5
    End With

this entire sheet is Auto-Generated and therefore theres no need to Insert COmments but am highlighting a particular cell with a dynamic comment but it overlaps the same cell which it is supposed to highlight..which is cell J8


SO please advise what needs to be done..

Warm Regards
e4excel

[e4excel]

Dear Forum,

THis comment contains the Last Opening Balance before the Opening of the Next Financial Year and therefore both the texts
i.e the Amount in the Cell J8 and the comment for this cell should be displayed, however as this comment is created dynamically it occupies the fixed position which is overlapping the cell and covering the Balance Amount in the cell J8 which is not serving the purpose..

So, can someone help me with changing the position of the comment more towards the Right so that both the TExt in the cell J8 and the Comment Text are clearly visible..

Warm Regards
e4excel

[e4excel]

I think after Googling I got the solution myself:

Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top - 20
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 10
Next
End Sub

WOuld appreciate if someone could just explain whats the Parent and TOP and LEFT parameters plzzzzzzzz


Regards
e

[romperstomper]

The Parent of a comment is the cell it is attached to. The Top and Left are the coordinates of the top and left sides of the object.

[e4excel]

Thanks for replying RomperStomper..

Then do we have Bottom and RIGHT too like LEFT and TOP or just "+" or "-" to change the co-ordinates..

Regards
E

[romperstomper]

No, but there are Height and Width properties so you just add those on.

[e4excel]

Thanks a lot for the Info RomperStomper..

I still have some new requiremetns regarding Highlighting the Comments Text but will start a new thread..

Warm Regards
e4excel