PDA

View Full Version : Solved: Adding and Deleting Comments



tonyrosen
12-20-2005, 01:23 PM
Okay, I have the following code:


Sub ValidateConsolidationSheet()
Dim x As Integer
Dim wSheets As Worksheet

Set wSheets = Worksheets("Sheet1")

For x = 1 To 5
If Range("EndingBalance" & x) <> Range("PerGL" & x) Then
wSheets.Unprotect SYSID
wSheets.Range("EndingBalance" & x).Interior.ColorIndex = 6
wSheets.Range("EndingBalance" & x).AddComment ("Ending Balance must equal PerGL82")
wSheets.Protect SYSID
Else
wSheets.Unprotect SYSID
wSheets.Range("EndingBalance" & x).Interior.ColorIndex = 0
wSheets.Protect SYSID
End If
Next x
End Sub



However, with the AddComment line, I get a VBA error: 400 (whatever THAT means).

What am I doing incorrectly?

malik641
12-20-2005, 01:36 PM
I thought that what you have should work....but give this a try:

Sub ValidateConsolidationSheet()
Dim x As Integer
Dim wSheets As Worksheet

Set wSheets = Worksheets("Sheet1")

For x = 1 To 5
If Range("EndingBalance" & x) <> Range("PerGL" & x) Then
wSheets.Unprotect SYSID
wSheets.Range("EndingBalance" & x).Interior.ColorIndex = 6

With wSheets.Range("EndingBalance" & x)
.AddComment
.Comment.Text Text:="Ending Balance must equal PerGL82"
End With

wSheets.Protect SYSID
Else
wSheets.Unprotect SYSID
wSheets.Range("EndingBalance" & x).Interior.ColorIndex = 0
wSheets.Protect SYSID
End If
Next x
End Sub
:thumb

tonyrosen
12-20-2005, 01:49 PM
It still gives me the "400" error :(

matthewspatrick
12-20-2005, 01:53 PM
Tony,

I see nothing obviously wrong with your code. It should work. The only thing I could think of is that maybe that cell already had a comment, but in that case the error should be 1004 (application-defined or object-defined error).

tonyrosen
12-20-2005, 01:57 PM
Doi!

You are correct ..... I'm an idiot ...

Sorry about what turned out to be a stupid question ...

However, now when I add a ClearComment line, I get the stupid error again.

tonyrosen
12-20-2005, 02:00 PM
AHA!

Got it!

I got it with ClearComments because it was still trying to put a comment in elsewhere where there already was one.

Thanks guys!

matthewspatrick
12-20-2005, 02:03 PM
Glad to help :beerchug:

austenr
12-20-2005, 02:03 PM
If your question has been answered successfully, please mark the thread solved!! :hi: