Run Time Error '91'

P

Paul3rd

Hello,
The following code causes Run Time Error '91' when I insert it into
worksheets.
The code allows comments to be shown when cell is active.
Can anyone help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
Nothing)
End Sub
Thanks in advance for any help,
Paul
 
P

Per Jessen

Hello,
The following code causes Run Time Error '91' when I insert it into
worksheets.
The code allows comments to be shown when cell is active.
Can anyone help?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4"))Is
Nothing)
End Sub
Thanks in advance for any help,
Paul

Hi Paul

The code is fine. Check if you have a comment in cell B4!

Regards,

Per
 
P

Paul3rd

Thanks for your quick reply.
You are correct!!
I didn't have a comment in the cell. Is there a way to modify the code so no
error is triggered if the cell is blank?
Thanks again,
Paul
 
D

Dave Peterson

And you can check for a comment in B4 in code with:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.Range("B4").Comment Is Nothing Then
'don't try to use .visible
Else
Me.Range("B4").Comment.Visible _
= Not (Intersect(Target, Range("B4")) Is Nothing)
End If
End Sub
 
P

Per Jessen

Thanks for your quick reply.
You are correct!!
I didn't have a comment in the cell. Is there a way to modify the code so no
error is triggered if the cell is blank?
Thanks again,
Paul







- Vis tekst i anførselstegn -

Hi Paul

Thanks for your reply!

This should do the trick

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("B4").Comment Is Nothing Then End
Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) Is
Nothing)

End Sub

Regards,

Per
 
P

Paul3rd

Thanks Dave,
That worked perfectly.
Paul

Dave Peterson said:
And you can check for a comment in B4 in code with:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.Range("B4").Comment Is Nothing Then
'don't try to use .visible
Else
Me.Range("B4").Comment.Visible _
= Not (Intersect(Target, Range("B4")) Is Nothing)
End If
End Sub
 
J

JLGWhiz

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Range("B4").Comment Is Nothing Then
Range("B4").Comment.Visible = Not (Intersect(Target, Range("B4")) _
Is Nothing)
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top