• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comment Auto-Size Not Working With 2007

dparteka

dparteka

Member
I’m using the macro shown below that works great on Excel 2003 but not so good on 2007. The way this should work is... a comment is created which includes the text “Employee No.: ”, the curser is automatically positioned at the end of that text which allows the user to type in their employee number. In 2003 while the user types in the comment box it auto-sizes while they are typing, in 2007 it does not auto-size until the user clicks inside the comment box after they are done typing, this prevents the users from seeing what they are typing while they are typing it, does anyone have a clue how to fix this?

[pre]
Code: 
Sub CreateComment()
With ActiveCell.AddComment
.Shape.Placement = xlMoveAndSize
.Shape.TextFrame.AutoSize = True
.Visible = False
.Text "Employee No.: "
Application.SendKeys "+{F2}"
End With
End Sub
[/pre]
 
Your code works fine in 2010,

so unless Autosize wasn't implement in 2007 I'm not sure


Why do you need the Send keys line ?

It works better without it

[pre]
Code: 
Sub CreateComment()
With ActiveCell.AddComment
.Shape.Placement = xlMoveAndSize
.Shape.TextFrame.AutoSize = True
.Visible = False
.Text "Employee No. la la la: "
'Application.SendKeys "+{F2}"
End With
End Sub
[/pre]
 
Hi ,


I am using Excel 2007 , and what I find is that your problem describes what happens when we manually insert a comment and format it.


There is a checkbox labelled Automatic size in the Comment properties , when you right-click in the cell , select Edit Comment , then right-click on the Comment and select Format Comment.


When this checkbox is unchecked , the comment box remains the same size , irrespective of the text entered in it ; as long as you are entering text into the comment box , the text keeps scrolling so that entered text is visible to the user. However , when you press the Esc key , the comment box does not resize so that the complete comment is not visible to the user.


When this checkbox is checked , the comment box remains the same size as long as text is being entered into the comment box , but the moment you press the Esc key , the comment box resizes so that the complete comment is visible to the user.


The behaviour of the macro is a correct reflection of the behaviour of the worksheet commands. Excel 2007 is an aberration in between the 2003 and 2010 versions !


Narayan
 
You must log in or register to reply here.
Back
Top