Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reqd with adding menu items!!
Hi yall, I am using vba in Excel 2002. I have code to add a top level menu to to the worksheet menu bar and two sub menu items under that - works fine. What I cant figure out is how to check for the existence of this menu before installing it on open. So every time this workbook is opening, it will look on the worksheet menu bar for a menu called "Tools" and if it is found, exit sub otherwise it will install it for the user. Many thanks in advance for any help offered. Kr, Kinny. Code creating menu items: Sub test() Set myMenuBar = CommandBars("Worksheet Menu Bar") Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, _ Temporary:=True) newMenu.Caption = "Tools" Set ctrl1 = newMenu.Controls _ ..Add(Type:=msoControlButton, ID:=1) With ctrl1 ..Caption = "Report Formatter" ..TooltipText = "Format Reports Automatically" ..Style = msoButtonCaption ..FaceId = 2 ..OnAction = "StartFormatting" End With Set ctrl2 = newMenu.Controls _ ..Add(Type:=msoControlButton, ID:=2) With ctrl2 ..Caption = "Report Builder" ..TooltipText = "Creates reports from SAS data source" ..Style = msoButtonCaption ..FaceId = 2 ..OnAction = "open_Report_Builder" End With End Sub -- kinny ------------------------------------------------------------------------ kinny's Profile: http://www.excelforum.com/member.php...o&userid=33899 View this thread: http://www.excelforum.com/showthread...hreadid=536737 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reqd with adding menu items!!
Take a different approach, delete it and then re-create it
On Error Resume Next With CommandBars("Worksheet Menu Bar").Controls("Tools") .Controls("Report Formatter").Delete .Controls("Report Builder").Delete End With On Error GoTo 0 then your code -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kinny" wrote in message ... Hi yall, I am using vba in Excel 2002. I have code to add a top level menu to to the worksheet menu bar and two sub menu items under that - works fine. What I cant figure out is how to check for the existence of this menu before installing it on open. So every time this workbook is opening, it will look on the worksheet menu bar for a menu called "Tools" and if it is found, exit sub otherwise it will install it for the user. Many thanks in advance for any help offered. Kr, Kinny. Code creating menu items: Sub test() Set myMenuBar = CommandBars("Worksheet Menu Bar") Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, _ Temporary:=True) newMenu.Caption = "Tools" Set ctrl1 = newMenu.Controls _ Add(Type:=msoControlButton, ID:=1) With ctrl1 Caption = "Report Formatter" TooltipText = "Format Reports Automatically" Style = msoButtonCaption FaceId = 2 OnAction = "StartFormatting" End With Set ctrl2 = newMenu.Controls _ Add(Type:=msoControlButton, ID:=2) With ctrl2 Caption = "Report Builder" TooltipText = "Creates reports from SAS data source" Style = msoButtonCaption FaceId = 2 OnAction = "open_Report_Builder" End With End Sub -- kinny ------------------------------------------------------------------------ kinny's Profile: http://www.excelforum.com/member.php...o&userid=33899 View this thread: http://www.excelforum.com/showthread...hreadid=536737 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reqd with adding menu items!!
Hi
Put this at the top of your code: Set myMenuBar = CommandBars("Worksheet Menu Bar") 'If Excel crashed while last opened so that Before_Close() event didn't happen 'the Timetable menubar may still exist. So delete it just in case On Error Resume Next myMenuBar.Controls("Tools").Delete On Error GoTo 0 In the same module you also want this sub: Public Sub Remove_Tools() Dim myMenuBar As CommandBar On Error Resume Next 'Incase it has already been deleted Set myMenuBar = CommandBars("Worksheet Menu Bar") cbWSMenuBar.Controls("Tools").Delete End Sub In the ThisWorkBook code module you want: Private Sub Workbook_Open() Call Test End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Msg As String Dim Ans As Variant 'If section stops Tools being deleted if Cancel is clicked on File Close message box If Not Me.Saved Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Me.Save Case vbNo Me.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If Call Remove_Tools End Sub If you go to close the file, then cancel that close, your menu will disappear. The code in the before close above stops that (code due to John Wolfenbach I think) There is also code in there to deal with the case where Excel might crash and the close event cannot delete the menu. regards Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reqd with adding menu items!!
Hi
Put this at the top of your code: Set myMenuBar = CommandBars("Worksheet Menu Bar") 'If Excel crashed while last opened so that Before_Close() event didn't happen 'the Timetable menubar may still exist. So delete it just in case On Error Resume Next myMenuBar.Controls("Tools").Delete On Error GoTo 0 In the same module you also want this sub: Public Sub Remove_Tools() Dim myMenuBar As CommandBar On Error Resume Next 'Incase it has already been deleted Set myMenuBar = CommandBars("Worksheet Menu Bar") cbWSMenuBar.Controls("Tools").Delete End Sub In the ThisWorkBook code module you want: Private Sub Workbook_Open() Call Test End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Msg As String Dim Ans As Variant 'If section stops Tools being deleted if Cancel is clicked on File Close message box If Not Me.Saved Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Me.Save Case vbNo Me.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If Call Remove_Tools End Sub If you go to close the file, then cancel that close, your menu will disappear. The code in the before close above stops that (code due to John Wolfenbach I think) There is also code in there to deal with the case where Excel might crash and the close event cannot delete the menu. regards Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help reqd with adding menu items!!
I don't have your application and I don't have any trouble finding a top
level menu named tools: ? Commandbars("Worksheet Menu Bar").Controls("Tools").Caption &Tools Seems like a poor choice of names. -- Regards, Tom Ogilvy "kinny" wrote: Hi yall, I am using vba in Excel 2002. I have code to add a top level menu to to the worksheet menu bar and two sub menu items under that - works fine. What I cant figure out is how to check for the existence of this menu before installing it on open. So every time this workbook is opening, it will look on the worksheet menu bar for a menu called "Tools" and if it is found, exit sub otherwise it will install it for the user. Many thanks in advance for any help offered. Kr, Kinny. Code creating menu items: Sub test() Set myMenuBar = CommandBars("Worksheet Menu Bar") Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, _ Temporary:=True) newMenu.Caption = "Tools" Set ctrl1 = newMenu.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl1 .Caption = "Report Formatter" .TooltipText = "Format Reports Automatically" .Style = msoButtonCaption .FaceId = 2 .OnAction = "StartFormatting" End With Set ctrl2 = newMenu.Controls _ .Add(Type:=msoControlButton, ID:=2) With ctrl2 .Caption = "Report Builder" .TooltipText = "Creates reports from SAS data source" .Style = msoButtonCaption .FaceId = 2 .OnAction = "open_Report_Builder" End With End Sub -- kinny ------------------------------------------------------------------------ kinny's Profile: http://www.excelforum.com/member.php...o&userid=33899 View this thread: http://www.excelforum.com/showthread...hreadid=536737 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding menu items | Excel Programming | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) | |||
adding shortcut menu items | Excel Programming | |||
adding shortcut menu items | Excel Programming | |||
Adding and Removing Custom Menu Items for one file... | Excel Programming |