| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
timestamp comments
I am looking for a small piece of code that will format a comment feild in
microsoft excel to start with the current date and time. this is details of what exactly the code needs to do: 1.Insert Comment 2.Code inserts date and timestamp into the newly opened comment field. 3.If you select "Edit Comment", it will create a two 's after the last typed word (paragraph). And then timestamp there. could anyone lend a hand? (well, alittle more than a hand) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Hi Ben
Only possible with code as far as I know See this page http://www.contextures.com/xlcomments01.html -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... I am looking for a small piece of code that will format a comment feild in microsoft excel to start with the current date and time. this is details of what exactly the code needs to do: 1.Insert Comment 2.Code inserts date and timestamp into the newly opened comment field. 3.If you select "Edit Comment", it will create a two 's after the last typed word (paragraph). And then timestamp there. could anyone lend a hand? (well, alittle more than a hand) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
helped me a few ways. show me how to add a comment, and how to remove the
username. however, i have no programming knowledge and wouldn't be able to complete the script :) so buddy, wanna just write it all for me? :/ (sorry for being lazy, don't have the time to start learning syntax.) "Ron de Bruin" wrote: Hi Ben Only possible with code as far as I know See this page http://www.contextures.com/xlcomments01.html -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... I am looking for a small piece of code that will format a comment feild in microsoft excel to start with the current date and time. this is details of what exactly the code needs to do: 1.Insert Comment 2.Code inserts date and timestamp into the newly opened comment field. 3.If you select "Edit Comment", it will create a two 's after the last typed word (paragraph). And then timestamp there. could anyone lend a hand? (well, alittle more than a hand) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Hi Ben
You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... helped me a few ways. show me how to add a comment, and how to remove the username. however, i have no programming knowledge and wouldn't be able to complete the script :) so buddy, wanna just write it all for me? :/ (sorry for being lazy, don't have the time to start learning syntax.) "Ron de Bruin" wrote: Hi Ben Only possible with code as far as I know See this page http://www.contextures.com/xlcomments01.html -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... I am looking for a small piece of code that will format a comment feild in microsoft excel to start with the current date and time. this is details of what exactly the code needs to do: 1.Insert Comment 2.Code inserts date and timestamp into the newly opened comment field. 3.If you select "Edit Comment", it will create a two 's after the last typed word (paragraph). And then timestamp there. could anyone lend a hand? (well, alittle more than a hand) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Or to add another date at the end of the existing comment, and open the
comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Thanks Debra for this version
Add it to your site I am sure others can use it to Ron -- Regards Ron de Bruin http://www.rondebruin.nl "Debra Dalgleish" wrote in message ... Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
thanks alot for all the replies, so far everything has worked correctly.
just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Hi Ben
Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd'
cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Hi Ben
In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
wow you guys are so helpful on these boards. i didn't realize those different
module things meant anything :) is it possible to add different options to the context menus? so i can make any list option link to a macro? "Ron de Bruin" wrote: Hi Ben In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Hi Ben
wow you guys are so helpful on these boards We try to me helpful This example will add 3 controls to the Cell menu that run your own macro's. Change this two lines : onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") Sub Add_Controls() Dim i As Long Dim onaction_names As Variant Dim caption_names As Variant onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(onaction_names) To UBound(onaction_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & onaction_names(i) .Caption = caption_names(i) End With Next i End With End Sub Sub Delete_Controls() Dim i As Long Dim caption_names As Variant caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... wow you guys are so helpful on these boards. i didn't realize those different module things meant anything :) is it possible to add different options to the context menus? so i can make any list option link to a macro? "Ron de Bruin" wrote: Hi Ben In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
why would i be gettign runtim eerror '91'? when i go to debug ti brings me
here : Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" "Ron de Bruin" wrote: Hi Ben wow you guys are so helpful on these boards We try to me helpful This example will add 3 controls to the Cell menu that run your own macro's. Change this two lines : onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") Sub Add_Controls() Dim i As Long Dim onaction_names As Variant Dim caption_names As Variant onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(onaction_names) To UBound(onaction_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & onaction_names(i) .Caption = caption_names(i) End With Next i End With End Sub Sub Delete_Controls() Dim i As Long Dim caption_names As Variant caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... wow you guys are so helpful on these boards. i didn't realize those different module things meant anything :) is it possible to add different options to the context menus? so i can make any list option link to a macro? "Ron de Bruin" wrote: Hi Ben In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Hi Ben
Show me the complete code that you use and tell me where you copy it -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be gettign runtim eerror '91'? when i go to debug ti brings me here : Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" "Ron de Bruin" wrote: Hi Ben wow you guys are so helpful on these boards We try to me helpful This example will add 3 controls to the Cell menu that run your own macro's. Change this two lines : onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") Sub Add_Controls() Dim i As Long Dim onaction_names As Variant Dim caption_names As Variant onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(onaction_names) To UBound(onaction_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & onaction_names(i) .Caption = caption_names(i) End With Next i End With End Sub Sub Delete_Controls() Dim i As Long Dim caption_names As Variant caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... wow you guys are so helpful on these boards. i didn't realize those different module things meant anything :) is it possible to add different options to the context menus? so i can make any list option link to a macro? "Ron de Bruin" wrote: Hi Ben In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
i fixed the problem, i was using multiple codes in different sheets cause i
thought it wouldn't matter, but i was wrong. anyways, i'm sure you'd know this: how would i make excel backup my excel files every 2minutes to a seperate HD? (not the auto-recover thing, a real backup) would it be easier to setup a batch file(would need to be alittle more powerful than a batch script to make exce save the file) to do this using the scheduler in windows? "Ron de Bruin" wrote: Hi Ben Show me the complete code that you use and tell me where you copy it -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be gettign runtim eerror '91'? when i go to debug ti brings me here : Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" "Ron de Bruin" wrote: Hi Ben wow you guys are so helpful on these boards We try to me helpful This example will add 3 controls to the Cell menu that run your own macro's. Change this two lines : onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") Sub Add_Controls() Dim i As Long Dim onaction_names As Variant Dim caption_names As Variant onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(onaction_names) To UBound(onaction_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & onaction_names(i) .Caption = caption_names(i) End With Next i End With End Sub Sub Delete_Controls() Dim i As Long Dim caption_names As Variant caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... wow you guys are so helpful on these boards. i didn't realize those different module things meant anything :) is it possible to add different options to the context menus? so i can make any list option link to a macro? "Ron de Bruin" wrote: Hi Ben In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
timestamp comments
Try this add-in
Autosafe.zip http://www.jkp-ads.com/Download.htm -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... i fixed the problem, i was using multiple codes in different sheets cause i thought it wouldn't matter, but i was wrong. anyways, i'm sure you'd know this: how would i make excel backup my excel files every 2minutes to a seperate HD? (not the auto-recover thing, a real backup) would it be easier to setup a batch file(would need to be alittle more powerful than a batch script to make exce save the file) to do this using the scheduler in windows? "Ron de Bruin" wrote: Hi Ben Show me the complete code that you use and tell me where you copy it -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be gettign runtim eerror '91'? when i go to debug ti brings me here : Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" "Ron de Bruin" wrote: Hi Ben wow you guys are so helpful on these boards We try to me helpful This example will add 3 controls to the Cell menu that run your own macro's. Change this two lines : onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") Sub Add_Controls() Dim i As Long Dim onaction_names As Variant Dim caption_names As Variant onaction_names = Array("macro1", "macro2", "macro3") caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(onaction_names) To UBound(onaction_names) With .Controls.Add(Type:=msoControlButton) .OnAction = ThisWorkbook.Name & "!" & onaction_names(i) .Caption = caption_names(i) End With Next i End With End Sub Sub Delete_Controls() Dim i As Long Dim caption_names As Variant caption_names = Array("caption 1", "caption 2", "caption 3") With Application.CommandBars("Cell") For i = LBound(caption_names) To UBound(caption_names) On Error Resume Next .Controls(caption_names(i)).Delete On Error GoTo 0 Next i End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... wow you guys are so helpful on these boards. i didn't realize those different module things meant anything :) is it possible to add different options to the context menus? so i can make any list option link to a macro? "Ron de Bruin" wrote: Hi Ben In the Thisworkbook module of your workbook add this two events Private Sub Workbook_Activate() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction _ = ThisWorkbook.Name & "!CommentDateTimeAdd" End Sub Private Sub Workbook_Deactivate() Application.CommandBars("Cell").FindControl(ID:=20 31).Reset End Sub In a normal module copy the macro Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.Text Text:=Format(Now, strDate) & Chr(10) Else cmt.Text Text:=cmt.Text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... why would i be getting the "The macro error 'Book1.xls!CommentDateTimeAdd' cannot be found. i knwo the macro is named correctly and ti is in the workbook. "Ron de Bruin" wrote: Hi Ben Code looks like this Sub ChangeControlMacro() Application.CommandBars("cell").FindControl(ID:=20 31).OnAction = ThisWorkbook.Name & "!TestMacro" 'to reset use 'Application.CommandBars("Cell").FindControl(ID:=2 031).Reset End Sub Sub TestMacro() MsgBox "Hi" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "BenMetz" wrote in message ... thanks alot for all the replies, so far everything has worked correctly. just curious though, can i arrange ti so either the "Insert Comment" in the context menu activates the macro? when i set a shortcut to the macro, it adds the date/time, but it doesn't open the comment. and are there any other time formats? like day fo the week? "Debra Dalgleish" wrote: Or to add another date at the end of the existing comment, and open the comment for editing: '========================= Sub CommentDateTimeAdd() Dim strDate As String Dim cmt As Comment Dim lBreak As Long Dim lArea As Long strDate = "dd-mmm-yy hh:mm:ss" Set cmt = ActiveCell.Comment If cmt Is Nothing Then Set cmt = ActiveCell.AddComment cmt.text text:=Format(Now, strDate) & Chr(10) Else cmt.text text:=cmt.text & Chr(10) _ & Format(Now, strDate) & Chr(10) End If With cmt.Shape.TextFrame .Characters.Font.Bold = False End With SendKeys "%ie~" End Sub '========================= Ron de Bruin wrote: Hi Ben You can run this macro if you want to add a comment with date/time stamp or change the date/time in the comment. Sub Test() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment ActiveCell.Comment.Text Text:=Format(Now, "dd-mmm-yy hh-mm-ss") Else If IsNumeric(Right(ActiveCell.Comment.Text, 2)) Then ActiveCell.Comment.Text Text:=Left(ActiveCell.Comment.Text, Len(ActiveCell.Comment.Text) - 18) _ & Format(Now, "dd-mmm-yy h-mm-ss") Else ActiveCell.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Format(Now, "dd-mmm-yy hh-mm-ss") End If End If End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Timestamp | Excel Discussion (Misc queries) | |||
| excel 2000 how to format the comments font all comments | Excel Discussion (Misc queries) | |||
| Timestamp | Excel Discussion (Misc queries) | |||
| Timestamp | Excel Discussion (Misc queries) | |||
| timestamp on each row... | Excel Programming | |||
Linear Mode
