Help reqd with adding menu items!!

[kinny]

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

 

[Bob Phillips]

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)

 

[paul.robinson]

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

 

[paul.robinson]

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

 

[Tom Ogilvy]

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.