Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Unanswered: Defining the location a comment shows

    I have a code that adds comments and fill with pics.

    Is it possible to define the location the comment will show when you mouse over. I know you can move around the comment box once it appers.

    The code I use is

    Sub Macro()

    Dim rng As Range
    Dim shp As Comment

    Set rng = ActiveCell

    If ActiveCell.Value = "Click to add pic" Then

    Dim fn
    fn = Application.GetOpenFilename
    If fn = False Then
    Set shp = rng.AddComment("")
    shp.Shape.Fill.UserPicture fn
    shp.Shape.Height = 322
    shp.Shape.Width = 465
    ActiveCell = "QC Pic"
    End If

    End If

    End Sub

  2. #2
    Join Date
    Jan 2002
    Bay Area
    This is how I add comments automatically to a range of cells. You need to have the data for the comments stored in a worksheet where you can read them. In my case, the comment text is in Sheets(2) in the same column and cells where they will be added to Sheet1. I test for text in column A on Sheet1 and stop adding comments when a blank cell in encountered, which in my application is the end of the data.

        While Trim(ActiveCell.Value) <> ""
            Range(ActiveCell.Address).Comment.Visible = False
            Range(ActiveCell.Address).Comment.Text _
                Text:=Sheets(2).Cells(ActiveCell.Row, ActiveCell.Column + 1).Value & ""
            ActiveCell.Offset(1, 0).Select
    Next I set properties of each comment to ensure they are not visible until the mouse is dragged over the cell, and resize the comments to reduce the size of the retangular comment box.

    HTML Code:
    Dim c As Comment
    For Each c In ActiveSheet.Comments
            c.Visible = False
            c.Shape.Locked = False
            c.Shape.TextFrame.AutoSize = True
        Next c
    I hope this helps in what you are doing.
    Good luck!

  3. #3
    Join Date
    Jan 2002
    Bay Area
    A correction--the comment text I add is actually text that is in column B of Sheets(2), which happens to be a name for a code in column A.

Posting Permissions

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