I have a large complex spreadsheet with 100's of comments that have
'spontaneously' taken on a variety of shapes and sizes. I applied VB code
(per Debra Dagleish) but get an Error 400 message when applied to this
spreadsheet. I have successfully applied the same macro to other
spreadsheets. Can someone help me identify the cause and implement a solution?

One place to start is to post the code you are using and give us more
information about the problem you have encountered with the one particular
sheet. Also, explain what YOU expect the code to do.

"Ditto" wrote:

I have a large complex spreadsheet with 100's of comments that have
'spontaneously' taken on a variety of shapes and sizes. I applied VB code
(per Debra Dagleish) but get an Error 400 message when applied to this
spreadsheet. I have successfully applied the same macro to other
spreadsheets. Can someone help me identify the cause and implement a solution?

The code I have tried to use is:

Sub Comments_AutoSize()
'posted by Dana DeLouis 2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub

I was hoping that this would take all the comments in the worksheet and
resize them the same, however I get an error 400 message each time, without
any effect on the comments.

"JLatham" wrote:

One place to start is to post the code you are using and give us more
information about the problem you have encountered with the one particular
sheet. Also, explain what YOU expect the code to do.

"Ditto" wrote:

I have a large complex spreadsheet with 100's of comments that have
'spontaneously' taken on a variety of shapes and sizes. I applied VB code
(per Debra Dagleish) but get an Error 400 message when applied to this
spreadsheet. I have successfully applied the same macro to other
spreadsheets. Can someone help me identify the cause and implement a solution?

I cannot duplicate your problem. I copied the code as published here and put
it into a module and it works fine (see note below), even on a sheet with no
comments at all, as it should.

One thing I did have to do to actually get any resizing done was change the
..Shape.TextFrame.AutoSize = True
statement to reverse the condition:
..Shape.TextFrame.AutoSize = False

and of course, I had to make the comment very wide before any change took
place, but with those in place, it worked fine for me in Excel 2003.

You might just delete the code entirely that you have and retype it into
either the same module again, or into a code module by itself and see if that
helps.

"Ditto" wrote:

The code I have tried to use is:

Sub Comments_AutoSize()
'posted by Dana DeLouis 2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub

I was hoping that this would take all the comments in the worksheet and
resize them the same, however I get an error 400 message each time, without
any effect on the comments.

"JLatham" wrote:

One place to start is to post the code you are using and give us more
information about the problem you have encountered with the one particular
sheet. Also, explain what YOU expect the code to do.

"Ditto" wrote:

I have a large complex spreadsheet with 100's of comments that have
'spontaneously' taken on a variety of shapes and sizes. I applied VB code
(per Debra Dagleish) but get an Error 400 message when applied to this
spreadsheet. I have successfully applied the same macro to other
spreadsheets. Can someone help me identify the cause and implement a solution?

Yes, I have not had difficulties with the macro on other spreadsheets, just
the problematic one. Unfortunately your suggestions havent provided relief
either. Could the problem be with the original spreadsheet?

"JLatham" wrote:

I cannot duplicate your problem. I copied the code as published here and put
it into a module and it works fine (see note below), even on a sheet with no
comments at all, as it should.

One thing I did have to do to actually get any resizing done was change the
.Shape.TextFrame.AutoSize = True
statement to reverse the condition:
.Shape.TextFrame.AutoSize = False

and of course, I had to make the comment very wide before any change took
place, but with those in place, it worked fine for me in Excel 2003.

You might just delete the code entirely that you have and retype it into
either the same module again, or into a code module by itself and see if that
helps.

"Ditto" wrote:

The code I have tried to use is:

Sub Comments_AutoSize()
'posted by Dana DeLouis 2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub

I was hoping that this would take all the comments in the worksheet and
resize them the same, however I get an error 400 message each time, without
any effect on the comments.

"JLatham" wrote:

One place to start is to post the code you are using and give us more
information about the problem you have encountered with the one particular
sheet. Also, explain what YOU expect the code to do.

"Ditto" wrote:

I have a large complex spreadsheet with 100's of comments that have
'spontaneously' taken on a variety of shapes and sizes. I applied VB code
(per Debra Dagleish) but get an Error 400 message when applied to this
spreadsheet. I have successfully applied the same macro to other
spreadsheets. Can someone help me identify the cause and implement a solution?

It very well could be something corrupted in either that particular worksheet
or workbook. You might try using File | Save As to write it to a different
file and hope that the new file doesn't have the problem. Or you could try
copying that one sheet to another workbook and seeing if it has the problem
in the new workbook. Use the Move/Copy option for the sheet and make a copy
into a new workbook, toss in the code into a module in it and try it out.

Otherwise, you may have to rebuild the sheet completely, and I know what a
pain that may turn out to be for you. Wish I had more to offer, but as you
know, the code is working with other sheets, it's working on another system
(mine) and so the problem would appear to narrow down to that one worksheet
itself.

One other thing to think about: does that problem sheet have any other Shape
objects on it? Think about 'drawing' objects such as ovals, rectangles,
point-outs, text boxes, etc. If so, perhaps one of them is confusing the
code - doesn't seem likely since the code should specifically be dealing with
Comment objects only, but it's something to think about as a remotely
possible maybe.

"Ditto" wrote:

Yes, I have not had difficulties with the macro on other spreadsheets, just
the problematic one. Unfortunately your suggestions havent provided relief
either. Could the problem be with the original spreadsheet?

"JLatham" wrote:

I cannot duplicate your problem. I copied the code as published here and put
it into a module and it works fine (see note below), even on a sheet with no
comments at all, as it should.

One thing I did have to do to actually get any resizing done was change the
.Shape.TextFrame.AutoSize = True
statement to reverse the condition:
.Shape.TextFrame.AutoSize = False

and of course, I had to make the comment very wide before any change took
place, but with those in place, it worked fine for me in Excel 2003.

You might just delete the code entirely that you have and retype it into
either the same module again, or into a code module by itself and see if that
helps.

"Ditto" wrote:

The code I have tried to use is:

Sub Comments_AutoSize()
'posted by Dana DeLouis 2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub

I was hoping that this would take all the comments in the worksheet and
resize them the same, however I get an error 400 message each time, without
any effect on the comments.

"JLatham" wrote:

One place to start is to post the code you are using and give us more
information about the problem you have encountered with the one particular
sheet. Also, explain what YOU expect the code to do.

"Ditto" wrote:

I have a large complex spreadsheet with 100's of comments that have
'spontaneously' taken on a variety of shapes and sizes. I applied VB code
(per Debra Dagleish) but get an Error 400 message when applied to this
spreadsheet. I have successfully applied the same macro to other
spreadsheets. Can someone help me identify the cause and implement a solution?