Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Inserting comments
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 21
Default Inserting comments
I recorded a macro to add a comment to a cell. This works fine as a
standalone macro.

When I add it to an existing sub routine. I get "Run-time error'1004'
Application-defined or object-defined error" The help file doesn't live up
to it's name on this one.

Can anyone help, please?

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
If Right(ComboBox1.Text, 3) = "D16" Or Right(ComboBox1.Text, 3) = "D32"
Then
Range("P37").Select
ActiveCell.FormulaR1C1 = "Delta software operation"
' Add comment code
Range("P37").Select
Range("P37").AddComment
Range("P37").Comment.Visible = False
Range("P37").Comment.Text Text:="Check image acquisition, recall and
manipulation."
'
Range("P38").Select
ActiveCell.FormulaR1C1 = "Image quality"
Range("P39").Select
ActiveCell.FormulaR1C1 = "Network operation"
Range("P40").Select
ActiveCell.FormulaR1C1 = "PC operation"

Range("P37:R40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders '(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Range("U37:V40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Else
'CheckBox74.Visible = False
End If



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Inserting comments
If a cell already has a comment can then the addcomment method will return
such an error. You can test and delete it with a construct like this

Sub AAA()
If Not Range("P37").Comment Is Nothing Then _
Range("P37").Comment.Delete

Range("P37").AddComment
End Sub

Regards,
Tom Ogilvy

"Ian Coates" wrote in message
...
I recorded a macro to add a comment to a cell. This works fine as a
standalone macro.

When I add it to an existing sub routine. I get "Run-time error'1004'
Application-defined or object-defined error" The help file doesn't live up
to it's name on this one.

Can anyone help, please?

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
If Right(ComboBox1.Text, 3) = "D16" Or Right(ComboBox1.Text, 3) = "D32"
Then
Range("P37").Select
ActiveCell.FormulaR1C1 = "Delta software operation"
' Add comment code
Range("P37").Select
Range("P37").AddComment
Range("P37").Comment.Visible = False
Range("P37").Comment.Text Text:="Check image acquisition, recall and
manipulation."
'
Range("P38").Select
ActiveCell.FormulaR1C1 = "Image quality"
Range("P39").Select
ActiveCell.FormulaR1C1 = "Network operation"
Range("P40").Select
ActiveCell.FormulaR1C1 = "PC operation"

Range("P37:R40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders '(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Range("U37:V40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Else
'CheckBox74.Visible = False
End If





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 21
Default Inserting comments
Thanks Tom. That did the trick :-)

In trying to simplify the code I've also discovered that it appears you
can't delete a comment if it isn't there. VBA can be so particular :-(

"Tom Ogilvy" wrote in message
...
If a cell already has a comment can then the addcomment method will return
such an error. You can test and delete it with a construct like this

Sub AAA()
If Not Range("P37").Comment Is Nothing Then _
Range("P37").Comment.Delete

Range("P37").AddComment
End Sub

Regards,
Tom Ogilvy

"Ian Coates" wrote in message
...
I recorded a macro to add a comment to a cell. This works fine as a
standalone macro.

When I add it to an existing sub routine. I get "Run-time error'1004'
Application-defined or object-defined error" The help file doesn't live
up
to it's name on this one.

Can anyone help, please?

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
If Right(ComboBox1.Text, 3) = "D16" Or Right(ComboBox1.Text, 3) = "D32"
Then
Range("P37").Select
ActiveCell.FormulaR1C1 = "Delta software operation"
' Add comment code
Range("P37").Select
Range("P37").AddComment
Range("P37").Comment.Visible = False
Range("P37").Comment.Text Text:="Check image acquisition, recall and
manipulation."
'
Range("P38").Select
ActiveCell.FormulaR1C1 = "Image quality"
Range("P39").Select
ActiveCell.FormulaR1C1 = "Network operation"
Range("P40").Select
ActiveCell.FormulaR1C1 = "PC operation"

Range("P37:R40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders '(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Range("U37:V40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Else
'CheckBox74.Visible = False
End If







Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Inserting comments
That is why I had:

If Not Range("P37").Comment Is Nothing Then _
Range("P37").Comment.Delete

--
Regards,
Tom Ogilvy

"Ian Coates" wrote in message
...
Thanks Tom. That did the trick :-)

In trying to simplify the code I've also discovered that it appears you
can't delete a comment if it isn't there. VBA can be so particular :-(

"Tom Ogilvy" wrote in message
...
If a cell already has a comment can then the addcomment method will
return
such an error. You can test and delete it with a construct like this

Sub AAA()
If Not Range("P37").Comment Is Nothing Then _
Range("P37").Comment.Delete

Range("P37").AddComment
End Sub

Regards,
Tom Ogilvy

"Ian Coates" wrote in message
...
I recorded a macro to add a comment to a cell. This works fine as a
standalone macro.

When I add it to an existing sub routine. I get "Run-time error'1004'
Application-defined or object-defined error" The help file doesn't
live
up
to it's name on this one.

Can anyone help, please?

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
If Right(ComboBox1.Text, 3) = "D16" Or Right(ComboBox1.Text, 3) =
"D32"
Then
Range("P37").Select
ActiveCell.FormulaR1C1 = "Delta software operation"
' Add comment code
Range("P37").Select
Range("P37").AddComment
Range("P37").Comment.Visible = False
Range("P37").Comment.Text Text:="Check image acquisition, recall
and
manipulation."
'
Range("P38").Select
ActiveCell.FormulaR1C1 = "Image quality"
Range("P39").Select
ActiveCell.FormulaR1C1 = "Network operation"
Range("P40").Select
ActiveCell.FormulaR1C1 = "PC operation"

Range("P37:R40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders '(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Range("U37:V40").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Else
'CheckBox74.Visible = False
End If









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting Comments Greggo G[_2_] Excel Discussion (Misc queries) 1 October 28th 09 12:13 PM
problems with inserting comments Derrick Excel Discussion (Misc queries) 1 August 19th 09 04:28 PM
Inserting comments milt Excel Worksheet Functions 3 March 28th 08 12:22 AM
inserting comments ? sunrosejenn Excel Discussion (Misc queries) 5 March 7th 06 02:44 PM
Inserting comments Ian Coates Excel Programming 0 February 2nd 04 03:20 PM


All times are GMT +1. The time now is 05:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement