Adding a comment to a cell in VBA?!
 

The dox are quite clear, do this...

ActiveSheet.Range(DestCPr & ocount).AddComment "Formula price"

But this gives me an "Application defined error".

Anyone have an idea?

Maury

Adding a comment to a cell in VBA?!
 

The first thought that hit me was that DestCPr and oCount didn't make a nice
address.

The second thought was that that cell already had a comment in it.

But this worked ok for me:

Option Explicit
Sub testme01()

Dim DestCPr As String
Dim oCount As Long

DestCPr = "A"
oCount = 1

With ActiveSheet.Range(DestCPr & oCount)
.ClearComments
.AddComment "Formula price"
End With
End Sub



Maury Markowitz wrote:

The dox are quite clear, do this...

ActiveSheet.Range(DestCPr & ocount).AddComment "Formula price"

But this gives me an "Application defined error".

Anyone have an idea?

Maury

--

Dave Peterson

Adding a comment to a cell in VBA?!
 

Two possibilities:

1. The expression "DestCPr & ocount" doesn't evaluate to
something that looks like a range.

2. The cell in question already contains a comment. In this
case you can do something like:

If Not ActiveSheet.Range(DestCPr & ocount).Comment Is Nothing Then
ActiveSheet.Range(DestCPr & ocount).Comment.Delete
End If

HTH
Andrew taylor


Maury Markowitz wrote:
The dox are quite clear, do this...

ActiveSheet.Range(DestCPr & ocount).AddComment "Formula price"

But this gives me an "Application defined error".

Anyone have an idea?

Maury

Adding a comment to a cell in VBA?!
 

Dave's beaten me to it but here's a similar code

Sub addComment()
Dim c
For Each c In Selection
c.addComment "Test Comment" & Chr(10) _
& "Line 2"
Next c
End Sub

Peter Atherton



"Maury Markowitz" wrote:

The dox are quite clear, do this...

ActiveSheet.Range(DestCPr & ocount).AddComment "Formula price"

But this gives me an "Application defined error".

Anyone have an idea?

Maury

Adding a comment to a cell in VBA?!
 

"Dave Peterson" wrote:

The first thought that hit me was that DestCPr and oCount didn't make a nice
address.

No, that's not it. The line immediately before it is identical with the
exception that it says .value = "10", which works fine.

The second thought was that that cell already had a comment in it.

This appears to be the problem. But the mystery simply deepens.

This is a brand new spreadsheet, created every day from scratch in the code.
No other code adds a comment, and in fact the sheet that's being worked on
didn't even exist 10 seconds earlier.

Maury

Adding a comment to a cell in VBA?!
 

Are you sure the correct sheet is active?

Is that activesheet protected?

What else does the error message say?

Maury Markowitz wrote:

"Dave Peterson" wrote:

The first thought that hit me was that DestCPr and oCount didn't make a nice
address.

No, that's not it. The line immediately before it is identical with the
exception that it says .value = "10", which works fine.

The second thought was that that cell already had a comment in it.

This appears to be the problem. But the mystery simply deepens.

This is a brand new spreadsheet, created every day from scratch in the code.
No other code adds a comment, and in fact the sheet that's being worked on
didn't even exist 10 seconds earlier.

Maury

--

Dave Peterson

Adding a comment to a cell in VBA?!
 

"Dave Peterson" wrote:

Are you sure the correct sheet is active?

Yes.

Is that activesheet protected?

No.

What else does the error message say?

Nothing, just the generic error, whatever it was.

Maury

Adding a comment to a cell in VBA?!
 

Sorry, I don't have another guess.

Maury Markowitz wrote:

"Dave Peterson" wrote:

Are you sure the correct sheet is active?

Yes.

Is that activesheet protected?

No.

What else does the error message say?

Nothing, just the generic error, whatever it was.

Maury

--

Dave Peterson

Adding a comment to a cell in VBA?!
 

Try something like this. It might give you some insight:

Sub EFGH()
Dim rng As Range
Dim cmt As Comment
Dim DestCPR As String
Dim ocount As Long

DestCPR = "A"
ocount = 10
On Error Resume Next
Set rng = Nothing
Set cmt = Nothing
Set rng = ActiveSheet.Range(DestCPR & ocount)
If rng Is Nothing Then
MsgBox "Invalid range"
Else
Set cmt = Nothing
Set cmt = rng.Comment
If Not cmt Is Nothing Then
MsgBox "cmt exist: " & cmt.Text
Exit Sub
End If
Set cmt = Nothing
Set cmt = rng.AddComment("Formula price")
If cmt Is Nothing Then
MsgBox "Range Good, Comment Bad"
End If
End If
On Error GoTo 0

End Sub

--
Regards,
Tom Ogilvy



"Maury Markowitz" wrote in
message ...
"Dave Peterson" wrote:

Are you sure the correct sheet is active?

Yes.

Is that activesheet protected?

No.

What else does the error message say?

Nothing, just the generic error, whatever it was.

Maury