Comments window

  • Thread starter Thread starter PNelson
  • Start date Start date
P

PNelson

Hi there,

I use a DB tool called RS3 for Excel (now known as
Accord for Excel - owned by Accelrys inc.). This tool
allows you to observe results with cell notes, these
commonly being the the average of the result values. the
cell notes is a popup window showing the individual result
values the average is calculated from. It only shows a
maximum of 5 of these values. Additional values can be
shown by choosing Tools>Options>View tab, and choosing
Comments & indicator. This shows the comments as stable
windows, which you can then resize. Now if you go back to
the same place in tools and choose Comment Indicator only,
the stable windows disappear, and they now popup only
again. We need this to allow us to be able to clearly see
all our data. However, the increase size of the popup
windows is not stable. How can I set Excel so that it
saves the increased size of the popups, both for every
query in this visit to the Excel tool, as well as future
visits (i.e. set it in the application or in the template)?

Thanks,

Paul.

PS this is Excel 2000
 
I'm not familiar with RS3 for Excel, so this may or may not help.
You can format each comment individually, by selecting the border,
then right-clicking and choosing format, and in the Alignment tab,
check the Resize to fit text checkbox. Click OK, then right-click
the comment and choose Set AutoShape Defaults to apply that format
to all future comments.

If RS3 is generating the comments with the Resize to fit text
property off, though, you can run this macro to reset all the
comments:

Public Sub AutosizeComments()
Dim cmComment As Comment
For Each cmComment In ActiveSheet.Comments
With cmComment.Shape.TextFrame
.WrapAutoText = True
.AutoSize = True
End With
Next cmComment
End Sub
 
Thanks very much for this reply - the Macro works for the
way we need it. It would be more convenient if the change
made in the "format comment" and set by "Set Autoshape
defaults" would be carried over once the cell formatting
has been changed back to "Comment indicator only". We need
to be able to see our Avg values (several in a single
column) and the comments (when required) separately - as
when viewed together, they cover each other up. However,
the Macro works, and we'll be able to use it.

Thanks very much for your help,

Paul.
 
Back
Top