Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "New Comment" command to all existing and new files.
Ok, I got this to work Code: -------------------- 'Add the 3 lines below to the "ThisWorkbook" module - make sure to un-comment. Public Sub Workbook_open() Custom_Comment End Sub Const strMenuName As String = "New Comment" Sub Custom_Comment() Dim cb As CommandBar, MenuObject As CommandBarPopup Dim NewSubMenu1 As CommandBarButton, NewSubMenu2 As CommandBarButton Dim NewSubMenu3 As CommandBarButton, NewSubMenu4 As CommandBarButton Const iBack1 = 9, iBack2 = 9, iBack3 = 9, iBack4 = 43 Const iText1 = 1, iText2 = 3, iText3 = 5, iText4 = 10 Remove_menu 'ensure no duplicates added Set cb = Application.CommandBars("Cell") Set MenuObject = cb.Controls.Add(Type:=msoControlPopup, temporary:=True) With MenuObject .Caption = strMenuName .BeginGroup = True With .Controls Set NewSubMenu1 = .Add(Type:=msoControlButton, temporary:=True) Set NewSubMenu2 = .Add(Type:=msoControlButton, temporary:=True) Set NewSubMenu3 = .Add(Type:=msoControlButton, temporary:=True) Set NewSubMenu4 = .Add(Type:=msoControlButton, temporary:=True) End With End With NewSubMenu1.Caption = "White Background - Black Text - 10pt" NewSubMenu1.OnAction = "'ApplyFormat " & iText1 & "," & iBack1 & "'" NewSubMenu2.Caption = "White Background - Red Text - 10pt" NewSubMenu2.OnAction = "'ApplyFormat " & iText2 & "," & iBack2 & "'" NewSubMenu3.Caption = "White Background - Blue Text - 10pt" NewSubMenu3.OnAction = "'ApplyFormat " & iText3 & "," & iBack3 & "'" NewSubMenu4.Caption = "Yellow Background - Green Text - 12pt" NewSubMenu4.OnAction = "'ApplyFormat " & iText4 & "," & iBack4 & "'" End Sub Sub Remove_menu() On Error Resume Next 'in case it isn't there Application.CommandBars("Cell").Controls(strMenuNa me).Delete End Sub Sub ApplyFormat(iText As Integer, iBack As Integer) On Error Resume Next ActiveCell.AddComment "" On Error GoTo 0 With ActiveSheet.Shapes(ActiveCell.Comment.Shape.Name) .Fill.ForeColor.SchemeColor = iBack With .TextFrame .HorizontalAlignment = xlHAlignCenter .VerticalAlignment = xlCenter .AutoSize = True .AutoMargins = False .MarginLeft = 7.2 .MarginRight = 7.2 .MarginTop = 7.2 .MarginBottom = 7.2 With .Characters.Font .Size = 10 .ColorIndex = iText .Name = "Arial" .Bold = False .Italic = False .Underline = False End With End With .Visible = msoCTrue .Select End With End Sub -------------------- I got the code from Bill Jelen's website. How can I make it so that this new command shows up on every excel file I open or new file I create? The company I work for doesn't let us change any window properties so I can't edit the format by changing the tool tips and this is the only way to format comments I've been able to find. Your help is greatly appreciated. Chris -- Chris Berry ------------------------------------------------------------------------ Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165 View this thread: http://www.excelforum.com/showthread...hreadid=567914 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "New Comment" command to all existing and new files.
Make the workbook with the code an addin and have all your users select that
addin in tools=addins. (place the addin on a share drive accessbile to everyone). Jan Karl Pieterse page on Addins/distributing applications http://www.jkp-ads.com/Articles/DistributeMacro00.htm -- Regards, Tom Ogilvy "Chris Berry" wrote: Ok, I got this to work Code: -------------------- 'Add the 3 lines below to the "ThisWorkbook" module - make sure to un-comment. Public Sub Workbook_open() Custom_Comment End Sub Const strMenuName As String = "New Comment" Sub Custom_Comment() Dim cb As CommandBar, MenuObject As CommandBarPopup Dim NewSubMenu1 As CommandBarButton, NewSubMenu2 As CommandBarButton Dim NewSubMenu3 As CommandBarButton, NewSubMenu4 As CommandBarButton Const iBack1 = 9, iBack2 = 9, iBack3 = 9, iBack4 = 43 Const iText1 = 1, iText2 = 3, iText3 = 5, iText4 = 10 Remove_menu 'ensure no duplicates added Set cb = Application.CommandBars("Cell") Set MenuObject = cb.Controls.Add(Type:=msoControlPopup, temporary:=True) With MenuObject .Caption = strMenuName .BeginGroup = True With .Controls Set NewSubMenu1 = .Add(Type:=msoControlButton, temporary:=True) Set NewSubMenu2 = .Add(Type:=msoControlButton, temporary:=True) Set NewSubMenu3 = .Add(Type:=msoControlButton, temporary:=True) Set NewSubMenu4 = .Add(Type:=msoControlButton, temporary:=True) End With End With NewSubMenu1.Caption = "White Background - Black Text - 10pt" NewSubMenu1.OnAction = "'ApplyFormat " & iText1 & "," & iBack1 & "'" NewSubMenu2.Caption = "White Background - Red Text - 10pt" NewSubMenu2.OnAction = "'ApplyFormat " & iText2 & "," & iBack2 & "'" NewSubMenu3.Caption = "White Background - Blue Text - 10pt" NewSubMenu3.OnAction = "'ApplyFormat " & iText3 & "," & iBack3 & "'" NewSubMenu4.Caption = "Yellow Background - Green Text - 12pt" NewSubMenu4.OnAction = "'ApplyFormat " & iText4 & "," & iBack4 & "'" End Sub Sub Remove_menu() On Error Resume Next 'in case it isn't there Application.CommandBars("Cell").Controls(strMenuNa me).Delete End Sub Sub ApplyFormat(iText As Integer, iBack As Integer) On Error Resume Next ActiveCell.AddComment "" On Error GoTo 0 With ActiveSheet.Shapes(ActiveCell.Comment.Shape.Name) .Fill.ForeColor.SchemeColor = iBack With .TextFrame .HorizontalAlignment = xlHAlignCenter .VerticalAlignment = xlCenter .AutoSize = True .AutoMargins = False .MarginLeft = 7.2 .MarginRight = 7.2 .MarginTop = 7.2 .MarginBottom = 7.2 With .Characters.Font .Size = 10 .ColorIndex = iText .Name = "Arial" .Bold = False .Italic = False .Underline = False End With End With .Visible = msoCTrue .Select End With End Sub -------------------- I got the code from Bill Jelen's website. How can I make it so that this new command shows up on every excel file I open or new file I create? The company I work for doesn't let us change any window properties so I can't edit the format by changing the tool tips and this is the only way to format comments I've been able to find. Your help is greatly appreciated. Chris -- Chris Berry ------------------------------------------------------------------------ Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165 View this thread: http://www.excelforum.com/showthread...hreadid=567914 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding "New Comment" command to all existing and new files.
Thanks a million Tom. -- Chris Berry ------------------------------------------------------------------------ Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165 View this thread: http://www.excelforum.com/showthread...hreadid=567914 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data only if adjacent to cell labeled "male"/"female" | Excel Worksheet Functions | |||
Excel not recognized using "open with" command on text files | Excel Discussion (Misc queries) | |||
Convert cell "contents" into a "comment" | Excel Discussion (Misc queries) | |||
Adding new elements to an existing "list" | Excel Worksheet Functions | |||
inserting a conditional "go to" command on a excel "if" function | Excel Worksheet Functions |