CreateComment Macro

[dparteka]

Could someone help combine these two macros into one? The macro creates a comment… sets the properties to “Move and Size with Cell”… sets it to “Auto Size”… positions it as shown in the “ResetComments” macro below… enters the text “Employee No.: “… and then opens the comment for editing with the active curser positioned to the far right


Sub CreateComment()

ActiveCell.AddComment

ActiveCell.Comment.Shape.Placement = xlMoveAndSize

ActiveCell.Comment.Shape.TextFrame.AutoSize = True

ActiveCell.Comment.Visible = False

ActiveCell.Comment.Text Text:="Employee No.: "

Application.SendKeys "+{F2}"

End Sub


Sub ResetComments()

Dim cmt As Comment

For Each cmt In ActiveSheet.Comments

cmt.Shape.Top = cmt.Parent.Top + 3

cmt.Shape.Left = _

cmt.Parent.Offset(0, 1).Left + 12

Next

End Sub

 

[SirJB7]

Hi, dparteka!

Both macros apply to different operations: the first one works on active cell (only one cell) and the second one works on all the comments on active worksheet.

If you want to adapt the 1st. macro to perform just as now with the additional feature of the 2nd. macro -but only for active cell-, you may try this:

-----

[pre]

Sub CreateComment()
With ActiveCell
.AddComment
With .Comment
With .Shape
.Placement = xlMoveAndSize
.TextFrame.AutoSize = True
.Top = .Parent.Top + 3
.Left = .Parent.Offset(0, 1).Left + 12
End With
.Visible = False
.Text Text:="Employee No.: "
End With
End With
Application.SendKeys "+{F2}"
End Sub

[/pre]
-----

I didn't try it, but the merge looks fine.

Regards!

 

[dparteka]

The macro stops at

.Top = .Parent.Top + 3

 

[SirJB7]

Hi, dparteka!

OK, let me try it and I'll advise you.

Regards!

 

[SirJB7]

Hi, dparteka!

Fixed and working.

-----

[pre]

Sub CreateCommentNew()
With ActiveCell
.ClearComments
.AddComment
With .Comment
With .Shape
.Placement = xlMoveAndSize
.TextFrame.AutoSize = True
.Top = ActiveCell.Comment.Parent.Top + 3
.Left = ActiveCell.Comment.Parent.Offset(0, 1).Left + 12
End With
.Visible = False
.Text Text:="Employee No.: "
End With
End With
Application.SendKeys "+{F2}"
End Sub

[/pre]
-----

There were missing 2 qualifications to ActiveCell.Comment before Parent.

I also added a ClearComments at the begining of the macro, because if you ran it on a cell that yet had a comment, it raised an error. The only problem is that you miss all the previous text in the comment.

I've checked for methods about comments, and just found AddComment and ClearComments. You can perform an update to the comment using Comment.Text and asigning it by code, but I don't know what you're typing inside each one.

Regards!

 

[dparteka]

SirJB7... do me a favor, run the macro and check the properties, on my end the object positioning is set to "Move but don't size with cells"... your macro calls for Placement = X1MoveAndSize

 

[SirJB7]

Hi, dparteka!


I've uploaded my test file to http://www.2shared.com/file/nfn4y01f/CreateComment_Macro__for_dpart.html


And it works smoothly as it did before when I posted the VBA code.

I'm running Excel 2010 and I don't get any problem. Which version are you using? On Thursday I can try it in a PC with 2007 version.


About Placement = xlMoveAndSize, it did nothing related to it, just unify the two macros as you firstly asked. This instruction (Placement...) is the third line in the first macro of your very first post. Check it, please. I didn't analyze what the code was exactly doing, I asummed it worked fine, and only got an issue when the cell had a comment yet.


I don't fully get what you meant with "Move but don't size with cells", I haven't that sentence/option in my version, but the three values that Placement accept are:

xlFreeFloating, 3, object floats freely

xlMove, 2, object moves with cells

xlMoveAndSize, 1, object moves and resizes with cells.


If any of this is suitable for you, please change it in the Placement statement. For what you described I suppose that is the second, xlMove.


Just advise if any trouble.


Regards!

 

[dparteka]

SirJB7... still using office 2003, the property options are...


#1 Move and size with cell

#2 Move but don't size with cell

#3 Don't move or size with cells


I'm assuming that if I run ActiveCell.Comment.Shape.Placement = xlMoveAndSize then the property would be is set to #1... but it sets to #2

 

[SirJB7]

Hi, dparteka!


Comparing the options of the two lists, I'm going to build a table.

[pre]

Value     Meaning
1       Move & size
2       Move & not size
3       Float (2010) or Not move & not size (2003)

[/pre]

So, your asumption appears to be right. Tried changing it to other option and tested what happened?


Regards!