Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Vertically center comment in cell

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there code I can add to the following that will vertically center the comment? Actually I would like the comment vertically centered in the cell located to the immediate right of the active cell (cell G2 in this case). I don't want the comment sized to fit the row height, just to be centered vertically within the existing row height.

    Sub test()
    Range("F2").Activate
    ActiveCell.ClearComments
    ActiveCell.AddComment
    ActiveCell.Comment.Visible = True
    ActiveCell.Comment.Shape.Select
    With Selection
    .ShapeRange.ScaleHeight 0.2, msoFalse, msoScaleFromTopLeft
    .ShapeRange.ScaleWidth 0.8, msoFalse, msoScaleFromTopLeft
    .ShapeRange.Fill.ForeColor.SchemeColor = 22
    .ShapeRange.IncrementLeft -7.25
    End With
    ActiveCell.Comment.Text Text:="this is a test"
    With ActiveCell.Comment.Shape.TextFrame.Characters.Font
    .Bold = True
    .ColorIndex = 5
    End With
    Range("F2").Activate
    End Sub

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Pinball

    Try this:

    Sub test()
    Range("F2").Activate
    ActiveCell.ClearComments
    ActiveCell.AddComment
    ActiveCell.Comment.Visible = True
    ActiveCell.Comment.Shape.Select
    With Selection
    .ShapeRange.ScaleHeight 0.2, msoFalse, msoScaleFromTopLeft
    .ShapeRange.ScaleWidth 0.8, msoFalse, msoScaleFromTopLeft
    .ShapeRange.Fill.ForeColor.SchemeColor = 22
    .ShapeRange.IncrementLeft -7.25
    If .ShapeRange.Height <= Rows(2).Height Then
    .ShapeRange.Top = Rows(2).Top + (Rows(2).Height - .ShapeRange.Height) / 2
    Else
    .ShapeRange.Top = Rows(2).Top - (.ShapeRange.Height - Rows(2).Height) / 2
    End If
    End With
    ActiveCell.Comment.Text Text:="this is a test"
    With ActiveCell.Comment.Shape.TextFrame.Characters.Font
    .Bold = True
    .ColorIndex = 5
    End With
    Range("F2").Activate
    End Sub

    If you wanted, you could set variables, but this should give you the general idea.

    Any help?

    Regards

    Robb__

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •