ExcelBanter - Inserting comments

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Inserting comments (https://www.excelbanter.com/excel-programming/289963-inserting-comments.html)

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

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

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

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