Defining the location a comment shows

Jay59 · #1 (**permalink**) 04-15-07, 15:36

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

Quote:

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
Else
Set shp = rng.AddComment("")
shp.Shape.Fill.UserPicture fn
shp.Shape.Height = 322
shp.Shape.Width = 465
ActiveCell = "QC Pic"
End If

Else
End If

End Sub

JerryDal · #2 (**permalink**) 05-04-07, 19:32

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.

Code:

Range("A2").Select
    While Trim(ActiveCell.Value) <> ""
        Range(ActiveCell.Address).AddComment
        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
    Wend

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!
Jerry

JerryDal · #3 (**permalink**) 05-04-07, 19:36

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.
Jerry

Internet Services

Web Development

Digital Marketing

Consumer Tech