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