Macro for special Insert comment text

[KP]

Hi,
Yesterday I asked the following question in this group:

When I right click "Insert comment", I want the date and time to be
standard in
the comment box just below the first default line with the username.

I want it to be available already when I open the file without having to
run
the macro manually or by shortcut selection.

In addition, is it possible - in the same macro - to change the first line
with username from bold to plain text.

--------------------------------------------------------------
I received the following answer:

To add the macro to the right-click menu...............

Add this to Thisworkbook module

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls("Insert Comment")
.OnAction = "Allskeds.xls" & "!CommentDateTimeAdd"
End With
End Sub

Gord Dibben MS Excel MVP
---------------------------------------------
However, this will not work. Probably because I have done something wrong.
The above macro has been inserted in Thisworkbook module.
When I right click Insert Comment, just the ordinary default username (with
bold) is inserted in the comment box and no date. The macro has no effect at
all.
Should it in addition be assigned in some other way?
If somebody could test the macro and tell me what to do to solve the
problem, I would appreciate it very much.
I am running Excel 2003.
Thank you.

K. Pedersen

 

[Gord Dibben]

No one can test unless the other macro I posted is present in the workbook.

Did you also copy the CommentDateTimeAdd macro into a general module in the
workbook?

Did you change "Allskeds.xls" to your workbook name?

That was the name of my workbook when I tested the macro and the workbook_open
event code.


Gord

 

[KP]

Hi,

Unfortunately, I have still problems with the macro.

1.
Yes the file name I use is Allskeds.xls

2.
Here I have placed the macro CommentDateTimeAdd:
VBAProject (Allskeds.xls)>Microsoft Excel Objects>Modules>Module1

3.
Here I have placed the Private Sub Workbook_Open
VBAProject (Allskeds.xls)>Microsoft Excel Objects>ThisWorkbook

The macro for use on the Worksheet Menu works fine. The problem is only the
right-click menu.
Opening the Allskeds file I receive the error message:
Run-time error "5"
Invalid procedure call or argument

and this line is marked with yellow colour:
With Application.CommandBars("Cell").Controls("Insert Comment")

I appreciate very much your help and hope you can figure out what I do
wrong.

Best regards
K. Pedersen

 

[Gord Dibben]

See in-line comments.

Hi,

Unfortunately, I have still problems with the macro.

1.
Yes the file name I use is Allskeds.xls

OK...if it wasn't you would get error message when you tried to run the macro

2.
Here I have placed the macro CommentDateTimeAdd:
VBAProject (Allskeds.xls)>Microsoft Excel Objects>Modules>Module1

Looks OK from your description.

3.
Here I have placed the Private Sub Workbook_Open
VBAProject (Allskeds.xls)>Microsoft Excel Objects>ThisWorkbook

That is correct module for this event code.

The macro for use on the Worksheet Menu works fine. The problem is only the
right-click menu.
Opening the Allskeds file I receive the error message:
Run-time error "5"
Invalid procedure call or argument

and this line is marked with yellow colour:
With Application.CommandBars("Cell").Controls("Insert Comment")

I cannot replicate that error on opening the workbook.

I appreciate very much your help and hope you can figure out what I do
wrong.

I can send you a working copy of a workbook or hopefully someone can respond
with some suggestions for the Run-time error "5"

 

[KP]

Hi,

Yes thank you, I would appreciate very much if you could send a working copy
of a workbook.
I guess this will enable me to find out what I have done wrong.
My email address is:
(e-mail address removed)

Regards,
K.Pedersen

 

[KP]

Hi Gord Dibben,
Thanks for the file.
My first quick test of the file resulted in the same trouble as earlier
already by opening the file.
Run-time error "5"
Invalid procedure call or argument.
It's late in Denmark now, and I will make some other tests tomorrow with
other computers and other versions of Excel.

Regards,
K. Pedersen

 

[Gord Dibben]

Glad to hear the run-time has gone away. I never would have guessed that you
were using a Danish version.

1. Here is revised code to keep the Comment open and ready to type.

Debra's original code included the sendkeys but I left it out for some strange
reason. Also dropped the linefeed that produced a blank row below the
date/time.

Sub CommentDateTimeAdd()
'adds comment with date and time and username

Dim strDate As String
Dim cmt As Comment

strDate = "dd-mmm-yy hh:mm:ss"
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Application.UserName & Chr(10) _
& Format(Now, strDate)
Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Format(Now, strDate)
End If

With cmt.Shape.TextFrame
.Characters.Font.Bold = False
End With

SendKeys "%ie~"

End Sub

For question 2, without locking the cell I don't know of a way to prevent
editing.

With cell locked you could not then delete the Comment.


Gord

 

[KP]

Hi,
Thanks for the revised code.
I have replaced the old code with the new one, but I did not succeed in
getting what I wanted.
Now when I right click "Insert comment" an Insert dialogue box appers asking
me to check if I want to move cells down or cells to the right. Moreover I
can choose one row or one column.
When I have checked one of the possibilities and click the OK button the
username and date is added but the comment box is not kept open.
Do you think this has something to do with my danishversion or do you have
other hints?

Regards,
K. Pedersen

 

[Gord Dibben]

The sendkeys line is same as Alt + i + e

Which by default is Insert>Edit Comment

Since you have re-programmed the Insert>Comment on the worksheet menu bar the
sendkeys will no longer give you the Edit Comment mode but some other shortcut
function.

On my Excel 2003 the sendkeys inserts and activates a new worksheet.

If you reset the Insert>Comment to default the Comment will stay open when you
use Insert Comment from the right-click menu.

It seems you just can't have everything<g>


Gord

 

[KP]

If you reset the Insert>Comment to default the Comment will stay open when
you
use Insert Comment from the right-click menu.

You mean reset the menu button? - and shall I still use all of the 3 macros?
I have now reset the worksheet menu bar as descibed here: "ExcelTips
Resetting Toolbars to Their Default.mht" and now I get the default Insert
comment text, so this works. This is all right because I only need the right
click possibility.

I have the following macros in my workbook:
ThisWorkbook: The two macro's from you (with danish text for "Insert")
Module1: The new macro with SendKeys (not modified by me)

Result:
The same dialogue box asking me to make a choise still appears and the
Comment box is not open after clicking Accept.
Moreover, I have no longer the possibility to choose Edit comment when I
right click the cell with the just inserted username and date.

Unless you can see something I have done wrong, I think we can't get this
case any closer and I have to give up the project.

Regards,
K. Pedersen

 

[Gord Dibben]

See in-line comments

You mean reset the menu button? - and shall I still use all of the 3 macros?
Yes

I have now reset the worksheet menu bar as descibed here: "ExcelTips
Resetting Toolbars to Their Default.mht" and now I get the default Insert
comment text, so this works. This is all right because I only need the right
click possibility.

I have the following macros in my workbook:
ThisWorkbook: The two macro's from you (with danish text for "Insert")
Module1: The new macro with SendKeys (not modified by me)

You should have the two events in Thisworkbook......workbook_open and
beforeclose

The macro goes into the Module1

Result:
The same dialogue box asking me to make a choise still appears and the
Comment box is not open after clicking Accept.

Maybe Alt + i + e means something else in Danish version.

In English version Alt + i + e means Insert>Edit Comment which is what

SendKeys "%ie~" does at the end of the macro

Does Alt + i drop down the Insert Menu?

If so, look for an underlined E. This is the command that will be executed when
you hit Alt + i + e

Moreover, I have no longer the possibility to choose Edit comment when I
right click the cell with the just inserted username and date.

That is what you should expect since you changed the default action of
right-click>Insert Comment.

 

[KP]

Hi again,
I am happy to inform you that you have given me the key to solve the problem
just before I gave up.
In my case I should hit Alt + I + R + R + enter to bring up the Comment box
to edit mode.
After this I changed SendKeys "%ie~" to SendKeys "%irr~" and everything
works perfectly.
Thank you very much for your help and your patience.

Best regards,
K. Pedersen


--------------------------------------------------------------------------------------------------------------------

 

[Gord Dibben]

Thanks for the feedback.

Just out of curiosity................

If Alt + i + e is Insert>Edit Comment in English what is Alt + R + R in Danish?


Gord

 

[KP]

Just out of curiosity................

If Alt + i + e is Insert>Edit Comment in English what is Alt + R + R in
Danish?

----------------------------------------------------------------------------------------------

Alt + R + R just brings up the Worksheet menu Insert with Edit Comment
active.
I have to press "r" twice because there is another one starting with the
letter "r"
Then as soon as I press Enter the Edit comment box appears.

In Danish it is: Indsaet>Rediger kommentar

K. Pedersen

 

[Gord Dibben]

Thanks KP

Happy we got you through this.


Gord

----------------------------------------------------------------------------------------------

Alt + R + R just brings up the Worksheet menu Insert with Edit Comment
active.
I have to press "r" twice because there is another one starting with the
letter "r"
Then as soon as I press Enter the Edit comment box appears.

In Danish it is: Indsaet>Rediger kommentar

K. Pedersen