Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding a hyper link to a cell comment | Excel Discussion (Misc queries) | |||
Adding a comment to a cell | New Users to Excel | |||
adding comment to cell | Excel Discussion (Misc queries) | |||
Adding a comment to a formula-not a cell | Excel Discussion (Misc queries) | |||
Adding Comment to cell | Excel Programming |