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