Code to see if Comment Exists

Hi,


I have 2 queries which are currently wrecking my head.

Do you know what the code is to see if a comment exists. If it exists I will amend the text and if it dosen't exist then I will enter a comment but I dont know how to see if there is alreay one active on the cell.
I thought it would be something like
If Cells(row_index, 2).Comment = True Then

but that is not it. Any ideas.

Also, when I run a macro that has popped upa number of forms , when they finish they dont seemto give the contrlol back to the excel screen. Excel is still on the screen but the blue bar at the top is slightly out of focus and you have to click on it to bring it back to vlue. Then you can edit the cells again.

Is there a command to send the screen back so excel will be active ?

Regards
Brian

for the second, possibly ActiveCell.Activate

Dim cmt as Comment
On Error Resume Next
set cmt = ActiveSheet.Range("B9").Comment
On Error goto 0
if not cmt is nothing then
' append text
Else
' add a comment
End if

--
Regards,
Tom Ogilvy


"wardides" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
>
> I have 2 queries which are currently wrecking my head.
>
> Do you know what the code is to see if a comment exists. If it exists I
> will amend the text and if it dosen't exist then I will enter a comment
> but I dont know how to see if there is alreay one active on the cell.
> I thought it would be something like
> If Cells(row_index, 2).Comment = True Then
>
> but that is not it. Any ideas.
>
> Also, when I run a macro that has popped upa number of forms , when
> they finish they dont seemto give the contrlol back to the excel
> screen. Excel is still on the screen but the blue bar at the top is
> slightly out of focus and you have to click on it to bring it back to
> vlue. Then you can edit the cells again.
>
> Is there a command to send the screen back so excel will be active ?
>
> Regards
> Brian
>
>
> --
> wardides
> ------------------------------------------------------------------------
> wardides's Profile:
http://www.excelforum.com/member.php...o&userid=15022
> View this thread: http://www.excelforum.com/showthread...hreadid=488734
>

Re. comments, I've always error trapped to check whether they exist. For
your needs I would do something like:


Dim myComm as comment
Dim c as range

set c = Activesheet.Cells(1, 1)

On Error Resume Next
Set myComm = c.Comment
On Error GoTo 0
If myComm Is Nothing Then
set myComm = c.Comments.add "My text"
Else
myComm.Text "My text"
End If
set c= nothing
set mycomm = nothing


wardides wrote:
> Hi,
>
>
> I have 2 queries which are currently wrecking my head.
>
> Do you know what the code is to see if a comment exists. If it exists I
> will amend the text and if it dosen't exist then I will enter a comment
> but I dont know how to see if there is alreay one active on the cell.
> I thought it would be something like
> If Cells(row_index, 2).Comment = True Then
>
> but that is not it. Any ideas.
>
> Also, when I run a macro that has popped upa number of forms , when
> they finish they dont seemto give the contrlol back to the excel
> screen. Excel is still on the screen but the blue bar at the top is
> slightly out of focus and you have to click on it to bring it back to
> vlue. Then you can edit the cells again.
>
> Is there a command to send the screen back so excel will be active ?
>
> Regards
> Brian
>
>

Thanks Tony.

Thats cleared it up thanks Lads.

Just some more options:

Sub Demo()
With ActiveCell
If .Comment Is Nothing Then
.AddComment "First Line" & vbLf
Else
.Comment.Text .Comment.Text & "More Lines" & vbLf
End If
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

HTH :>)
--
Dana DeLouis
Win XP & Office 2003


"wardides" <[email protected]> wrote in
message news:[email protected]...
>
> Thats cleared it up thanks Lads.
>
>
> --
> wardides
> ------------------------------------------------------------------------
> wardides's Profile:
> http://www.excelforum.com/member.php...o&userid=15022
> View this thread: http://www.excelforum.com/showthread...hreadid=488734
>

Neater! Why didn't I think of that....

Dana DeLouis wrote:
> Just some more options:
>
> Sub Demo()
> With ActiveCell
> If .Comment Is Nothing Then
> .AddComment "First Line" & vbLf
> Else
> .Comment.Text .Comment.Text & "More Lines" & vbLf
> End If
> .Comment.Shape.TextFrame.AutoSize = True
> End With
> End Sub
>
> HTH :>)