leave Comment in edit mode after code . .

[staticbob]

Hi,

I have some code that colour codes a cell, then adds a comment with details. Can I leave this comment box in edit mode after the code has ran ?

Cheers
Bob

[Mudface]

Yes, but you'll need to make it visible first, I think. Something like should work OK (providing you don't already have a comment in Range("A1")): -

Sub EditComment()
Dim cmt As Comment

Set cmt = Range("a1").AddComment 'Set cmt = Range("A1").Comment if you already have one there
With cmt
    .Visible = True
    .Shape.Select True
End With

End Sub

[staticbob]

Cheers Mudface,

I've got this code now, but it doesn't leave the comment in edit mode.

Sub Red()
Selection.Interior.ColorIndex = 3
ActiveCell.AddComment
ActiveCell.comment.Visible = True
ActiveCell.comment.Text Text:="Date informed:"
ActiveCell.comment.Shape.Select True
End Sub

Still not working ?

Thanks.

[Mudface]

Works for me. Run it and then start typing, the text will go straight into the comment.

[staticbob]

Sorry mate, I didn't actually try that, just saw that the box was selected not in edit mode.

Thanks

[Mudface]

No problem, it's not immediately obvious, other than there isn't an active cell.

[staticbob]

Is there anyway of then hiding the box automatically once the user has completed details ? So when they click off it ?

Obviously, by making it visible earlier in the code it stays visible.

Either that, or I'll bang a button on screen to "Hide all Comments" that will do just that.

[Mudface]

I can't think of a simple way of doing that, so your second option might be a better way to go.

[jimboy]

How about a worksheet_change event to switch off the comments show?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

OR

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

[staticbob]

Hello again,

I am using this code to colour a holiday chart, then using the countbycolor function to count an employees totals. I was trying to update the countbycolor calcs as users shade the cells. The Calculate step takes a few seconds (4) though, not as quick as F9. Is there a quicker way to do this, rather than telling my users to hit F9, which I know they will forget about.

Sub Red()
Selection.Interior.ColorIndex = 38
ActiveCell.AddComment
ActiveCell.comment.Visible = True
ActiveCell.comment.Text Text:="Date informed:"
ActiveCell.comment.Shape.Select True
Calculate
End Sub


Cheers
Bob