2003 macro not functioning correctly in 2007

[jeffrey]

i had a macro to create a blank comment, without wasting space with my
name in it, leaving it open for me to enter text.

when i installed 2007 it it doesn't operate correctly anymore. 1st it
places a blank comment but doesn't stay open for me enter text. and 2. it
moves the cell down one space.

i did try to record a new macro, but macro record won't accept a stop
recording command with a blank comment open.

any ideas on how i can get the past macro to function properly again, or how
i can get excel to accept the recording of a new macro?

 

[Don Guillett]

I just created this in 2003 and saved as .xls. Opened in xl2007>saved as
..xlsx file. Worked fine in both xl2003 as and .xls and in 2007 as either.

Sub addcomment()
With Range("M24")
.addcomment
.Comment.Visible = False
'use this line to take out the name
.Comment.Text Text:="" & Chr(10) & ""
'use this line to add text. Line above NOT necessary when using line below
.Comment.Text Text:="Hi There"
End With
End Sub

 

[jeffrey]

don, thanks for the code. it does just as you say. i makes a comment with
"hi there" at a certain cell. i want to have a comment open, blank, with
cursor in it, ready to enter text, at the cell that is highlighted when i
click the macro. here is the code that worked in 2003, but doesn't in 2007:

Sub CommentAddOrEditTNR()

'adds TimesNewRoman comment or positions

'cursor at end of existing comment text

Dim cmt As Comment

Set cmt = ActiveCell.Comment

If cmt Is Nothing Then

ActiveCell.AddComment Text:=""

Set cmt = ActiveCell.Comment

With cmt.Shape.TextFrame.Characters.Font

.Name = "Times New Roman"

.Size = 11

.Bold = False

.ColorIndex = 0

End With

End If

SendKeys "%ie~"

End Sub





jeffrey


--
jeffrey

I just created this in 2003 and saved as .xls. Opened in xl2007>saved as
.xlsx file. Worked fine in both xl2003 as and .xls and in 2007 as either.

Sub addcomment()
With Range("M24")
.addcomment
.Comment.Visible = False
'use this line to take out the name
.Comment.Text Text:="" & Chr(10) & ""
'use this line to add text. Line above NOT necessary when using line
below
.Comment.Text Text:="Hi There"
End With
End Sub

 

[Don Guillett]

The trick is to add the text FIRST by using an inputbox. Sendkeys is VERY
unreliable. I am available as a consultant/developer.

Sub addcomment1()
mymsg = InputBox("Add text now")
'MsgBox mymsg
With ActiveCell
.addcomment
.Comment.Visible = False
.Comment.Text Text:=mymsg
End With
End Sub

or even better

Sub addcomment1()
With ActiveCell
..addcomment
..Comment.Visible = False
..Comment.Text Text:=InputBox("Add text now")
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

don, thanks for the code. it does just as you say. i makes a comment with
"hi there" at a certain cell. i want to have a comment open, blank, with
cursor in it, ready to enter text, at the cell that is highlighted when i
click the macro. here is the code that worked in 2003, but doesn't in
2007:

Sub CommentAddOrEditTNR()

'adds TimesNewRoman comment or positions

'cursor at end of existing comment text

Dim cmt As Comment

Set cmt = ActiveCell.Comment

If cmt Is Nothing Then

ActiveCell.AddComment Text:=""

Set cmt = ActiveCell.Comment

With cmt.Shape.TextFrame.Characters.Font

.Name = "Times New Roman"

.Size = 11

.Bold = False

.ColorIndex = 0

End With

End If

SendKeys "%ie~"

End Sub





jeffrey

 

[Dave Peterson]

Did you also switch to Vista (from WinXP) when you switched to xl2007.

http://groups.google.co.uk/group/mi...c/444f555485bb0cab?lnk=st&q=#444f555485bb0cab

or

http://snipurl.com/25lkx

 

[jeffrey]

nope, no vista here.

--
jeffrey

Did you also switch to Vista (from WinXP) when you switched to xl2007.

http://groups.google.co.uk/group/mi...c/444f555485bb0cab?lnk=st&q=#444f555485bb0cab

or

http://snipurl.com/25lkx

 

[jeffrey]

don, thank you, this will be ok.

jeffrey