|
Macro for special Insert comment text
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 |
Macro for special Insert comment text
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 On Fri, 11 Jan 2008 23:59:41 +0100, "KP" wrote: 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 |
Macro for special Insert comment text
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 ObjectsModulesModule1 3. Here I have placed the Private Sub Workbook_Open VBAProject (Allskeds.xls)Microsoft Excel ObjectsThisWorkbook 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" <gorddibbATshawDOTca skrev i en meddelelse ... 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 On Fri, 11 Jan 2008 23:59:41 +0100, "KP" wrote: 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 |
Macro for special Insert comment text
See in-line comments.
On Sat, 12 Jan 2008 13:07:22 +0100, "KP" wrote: 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 ObjectsModulesModule1 Looks OK from your description. 3. Here I have placed the Private Sub Workbook_Open VBAProject (Allskeds.xls)Microsoft Excel ObjectsThisWorkbook 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" Best regards K. Pedersen Gord "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse .. . 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 On Fri, 11 Jan 2008 23:59:41 +0100, "KP" wrote: 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 |
Macro for special Insert comment text
|
Macro for special Insert comment text
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" <gorddibbATshawDOTca skrev i en meddelelse ... See in-line comments. On Sat, 12 Jan 2008 13:07:22 +0100, "KP" wrote: 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 ObjectsModulesModule1 Looks OK from your description. 3. Here I have placed the Private Sub Workbook_Open VBAProject (Allskeds.xls)Microsoft Excel ObjectsThisWorkbook 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" Best regards K. Pedersen Gord "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse . .. 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 On Fri, 11 Jan 2008 23:59:41 +0100, "KP" wrote: 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 |
Macro for special Insert comment text
Hi,
Just to be quite sure. Should your file and the right click Insert Comment work as is on any computer without doing anything else? Or does the right click insert macro function not work until I have assigned the macro to the two Insert Comment buttons? Is there any other settings I should be aware of? K. Pedersen "KP" skrev i en meddelelse ... 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" <gorddibbATshawDOTca skrev i en meddelelse ... See in-line comments. On Sat, 12 Jan 2008 13:07:22 +0100, "KP" wrote: 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 ObjectsModulesModule1 Looks OK from your description. 3. Here I have placed the Private Sub Workbook_Open VBAProject (Allskeds.xls)Microsoft Excel ObjectsThisWorkbook 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" Best regards K. Pedersen Gord "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse ... 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 On Fri, 11 Jan 2008 23:59:41 +0100, "KP" wrote: 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 |
Macro for special Insert comment text
Hi,
I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen "KP" skrev i en meddelelse ... Hi, Just to be quite sure. Should your file and the right click Insert Comment work as is on any computer without doing anything else? Or does the right click insert macro function not work until I have assigned the macro to the two Insert Comment buttons? Is there any other settings I should be aware of? K. Pedersen "KP" skrev i en meddelelse ... 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" <gorddibbATshawDOTca skrev i en meddelelse ... See in-line comments. On Sat, 12 Jan 2008 13:07:22 +0100, "KP" wrote: 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 ObjectsModulesModule1 Looks OK from your description. 3. Here I have placed the Private Sub Workbook_Open VBAProject (Allskeds.xls)Microsoft Excel ObjectsThisWorkbook 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" Best regards K. Pedersen Gord "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse ... 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 On Fri, 11 Jan 2008 23:59:41 +0100, "KP" wrote: 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 |
Macro for special Insert comment text
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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
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" <gorddibbATshawDOTca skrev i en meddelelse ... 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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
The sendkeys line is same as Alt + i + e
Which by default is InsertEdit Comment Since you have re-programmed the InsertComment 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 InsertComment 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 On Sun, 13 Jan 2008 19:58:44 +0100, "KP" wrote: 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" <gorddibbATshawDOTca skrev i en meddelelse .. . 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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
You wrote:
If you reset the InsertComment 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 he "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" <gorddibbATshawDOTca skrev i en meddelelse ... The sendkeys line is same as Alt + i + e Which by default is InsertEdit Comment Since you have re-programmed the InsertComment 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 InsertComment 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 On Sun, 13 Jan 2008 19:58:44 +0100, "KP" wrote: 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" <gorddibbATshawDOTca skrev i en meddelelse . .. 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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
See in-line comments
On Sun, 13 Jan 2008 22:58:37 +0100, "KP" wrote: You wrote: If you reset the InsertComment 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? Yes I have now reset the worksheet menu bar as descibed he "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 InsertEdit 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-clickInsert Comment. 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 "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse .. . The sendkeys line is same as Alt + i + e Which by default is InsertEdit Comment Since you have re-programmed the InsertComment 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 InsertComment 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 On Sun, 13 Jan 2008 19:58:44 +0100, "KP" wrote: 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" <gorddibbATshawDOTca skrev i en meddelelse ... 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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
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" <gorddibbATshawDOTca skrev i en meddelelse ... See in-line comments On Sun, 13 Jan 2008 22:58:37 +0100, "KP" wrote: You wrote: If you reset the InsertComment 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? Yes I have now reset the worksheet menu bar as descibed he "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 InsertEdit 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-clickInsert Comment. 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 "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse . .. The sendkeys line is same as Alt + i + e Which by default is InsertEdit Comment Since you have re-programmed the InsertComment 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 InsertComment 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 On Sun, 13 Jan 2008 19:58:44 +0100, "KP" wrote: 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" <gorddibbATshawDOTca skrev i en meddelelse m... 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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
Thanks for the feedback.
Just out of curiosity................ If Alt + i + e is InsertEdit Comment in English what is Alt + R + R in Danish? Gord On Mon, 14 Jan 2008 01:56:53 +0100, "KP" wrote: 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" <gorddibbATshawDOTca skrev i en meddelelse .. . See in-line comments On Sun, 13 Jan 2008 22:58:37 +0100, "KP" wrote: You wrote: If you reset the InsertComment 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? Yes I have now reset the worksheet menu bar as descibed he "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 InsertEdit 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-clickInsert Comment. 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 "Gord Dibben" <gorddibbATshawDOTca skrev i en meddelelse ... The sendkeys line is same as Alt + i + e Which by default is InsertEdit Comment Since you have re-programmed the InsertComment 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 InsertComment 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 On Sun, 13 Jan 2008 19:58:44 +0100, "KP" wrote: 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" <gorddibbATshawDOTca skrev i en meddelelse om... 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 On Sun, 13 Jan 2008 15:51:43 +0100, "KP" wrote: Hi, I am happy to report that I have now solved the problem and I apoligize for the inconvenience caused. In the macro text I just had to change "Insert Comment" to the name of the function in the danish version. Quite simple and I should have been aware of that in the first place. Now I want to make a change to the macro and I am asking for further help. The change I want is as follows: 1. When I right click "Insert comment" I want the comment box NOT to disappear until I click another cell. I want the comment box open, ready to type in with the cursor active. 2. Is it possible to protect the automatically inserted username and date so that it cannot be deleted by editing with backspace or Delete on keyboard. The possibility to delete by right click "Delete comment" is all right. Regards K. Pedersen |
Macro for special Insert comment text
Just out of curiosity................
If Alt + i + e is InsertEdit 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: IndsaetRediger kommentar K. Pedersen |
| All times are GMT +1. The time now is 07:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com