Dave, I'm trying to speed things up, is your solution faster than what we
already do which is to add them one by one as they come up thru a call to
something like ...
Private Sub InsertComment(lCol As Long, lRow As Long, sType As String,
sValue As String, lColor As Long)
Dim sTmpCell As String
Dim sComment As String
Const PROC_NAME = "InsertComment"
On Error GoTo ERR_HANDLER
sTmpCell = GetCellAddress(lCol, lRow)
With Range(sTmpCell)
If .Comment Is Nothing Then
.AddComment
.Comment.Text Text:=sType & sValue & vbNullString
Else
sComment = .Comment.Text
.Comment.Text Text:=sComment & vbLf & sType & sValue & vbNullString
End If
.Comment.Visible = False
.Comment.Shape.TextFrame.Characters.Font.Size = 8
.Comment.Shape.TextFrame.Characters.Font.Bold = False
.Comment.Shape.TextFrame.Characters.Font.Color = vbBlue
End With
Exit Sub
ERR_HANDLER:
Call AppError(err, MODULE_NAME, PROC_NAME)
End Sub
?
"Dave Peterson" wrote:
> I think you're going to have to loop through the cells and loop through the
> comments.
>
> Dim aRowComment As String
> Dim myCell As Range
> dim cCtr as long
> dim aRowComment as variant
>
> arowcomment = array("test","test2","test3")
>
> cctr = lbound(arowcomment)
> With ActiveSheet
> with .Range(.Cells(lRow, lOrigCol), .Cells(lRow, lCol))
> if .cells.count <> ubound(arowcomment) - lbound(arowcomment) + 1 then
> 'not enough comments for all the cells!
> exit sub
> end if
>
> 'clear existing comments
> .ClearComments
> For Each myCell In .Cells
> myCell.AddComment Text:=aRowComment(cctr)
> cctr = cctr + 1
> Next myCell
> End With
> End With
>
> I have no idea if this puts the comments where you want them--test it before you
> trust it.
>
> You may want to loop through each row--or loop through each column.
>
>
>
> DB042188 wrote:
> >
> > I'm trying to add comments to a range rather than one by one as follows...
> >
> > Range(Cells(lRow, lOrigCol), Cells(lRow, lCol)).Comment = aRowComment
> >
> > ...in an effort to get better run times but get the following error
> >
> > object variable or with block variable not set
> >
> > ...when I append .Text on the end of the command I get a compile error
> > saying assignment to a constant not permitted
> >
> > aRowComment is an array of comments, built similarly to the array of values
> > currently transferring to the range with no problems.
>
> --
>
> Dave Peterson
>
|
Similar Threads
