Hi Ron

As you indicated in your email

€�I also added the examples to my site€�
http://www.rondebruin.com/menuid.htm

I picked up the last procedure needed from your website.

The complete menu manipulation I was trying to accomplish
that you wrote the procedures for works great.

My sincere thanks for the procedures and an excellent Job.

Good Luck
TK


"Ron de Bruin" wrote:

I update the page with a few new examples
http://www.rondebruin.com/menuid.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
Ron de Bruin might have some better first hand experience for you. He
seemed to be doing "dictator" applications when he first started posting
here, so replacing the Worksheet Menu Bar would be a standard for that type
of app.

I don't think he addesses that here, but there is some good information:

http://www.rondebruin.com/menuid.htm

send him an email and I am sure he can give you some good advice.

--
Regards,
Tom Ogilvy

"TK" wrote in message
...
Thanks Tom

Your comment

there are many ways to go with this

becomes more obvious with each article I read and I have been
researching this on and off for a week or so.
I also though I would receive a reply from a menu person.
I have seen some pretty good replies concerning menus
in the post I researched.
Anyway I think I have enough info and test examples now
to go forward.

Thanks again for your time, very much appreciated!
TK


"Tom Ogilvy" wrote:

the type property determines if it is a "Worksheet Menu Bar" type. the
type is: msoBarTypeMenuBar

You could possibly create this with code, then go into customize mode
and
drag (copy) commandbars to it.

Here is some code modified from


http://msdn.microsoft.com/library/de...commandbar.asp

this only copies the top level controls when you do it for the Worksheet
menu bar so it will need some work - this isn't an area I have done a
lot of
work in so someone else may have a better idea.

Sub Tester1()
Dim sOriginal As String
Dim sCopy As String
sOriginal = "Worksheet Menu Bar"
sCopy = "New Worksheet Menu Bar"
CBCopyCommandBar sOriginal, sCopy, True

End Sub


Function CBCopyCommandBar(strOrigCBName As String, _
strNewCBName As String, _
Optional blnShowBar As Boolean = False) As
Boolean

' This procedure copies the command bar named in the strOrigCBName
' argument to a new command bar specified in the strNewCBName
argument.

Dim cbrOriginal As CommandBar
Dim cbrCopy As CommandBar
Dim ctlCBarControl As CommandBarControl
Dim lngBarType As Long

On Error GoTo CBCopy_Err

Set cbrOriginal = CommandBars(strOrigCBName)

lngBarType = cbrOriginal.Type
Select Case lngBarType
Case msoBarTypeMenuBar
Set cbrCopy = CommandBars.Add(Name:=strNewCBName,
Position:=msoBarMenuBar)
Case msoBarTypePopup
Set cbrCopy = CommandBars.Add(Name:=strNewCBName,
Position:=msoBarPopup)
Case Else
Set cbrCopy = CommandBars.Add(Name:=strNewCBName)
End Select

' Copy controls to new command bar.
For Each ctlCBarControl In cbrOriginal.Controls
ctlCBarControl.Copy cbrCopy
Next ctlCBarControl

' Show new command bar.
If blnShowBar = True Then
If cbrCopy.Type = msoBarTypePopup Then
cbrCopy.ShowPopup
ElseIf cbrCopy.Type = msoBarTypeNormal Then
cbrCopy.Visible = True
ElseIf cbrCopy.Type = msoBarTypeMenuBar Then
cbrOriginal.Enabled = False
cbrCopy.Visible = True
cbrCopy.Enabled = True
End If
End If
CBCopyCommandBar = True
CBCopy_End:
Exit Function
CBCopy_Err:
CBCopyCommandBar = False
Resume CBCopy_End
End Function

--
Regards,
Tom Ogilvy



"TK" wrote in message
...
Thanks Tom

If I can indulge you with one more question:
I book marked those sites. One I think explains how to build a
"Worksheet Menu Bar" with code.
As you pointed out in your first reply building toolbars is pretty
straight forward; however, I haven't been able to replicate either
the "Worksheet Menu Bar" or the "Chart Menu Bar". Do these have
to be built with code or can they be duplicated in the customized
box.

Thanks again
TK


"Tom Ogilvy" wrote:

There is no saveAs option for commandbars. If you do the customize
route,
then your changes will be saved in the .xlb file and the bar will be
remain
in that customized state until someone resets it or moves/renames
the
..xlb
file or re-cusstomizes it.

there are many ways to go with this so maybe a little background
information
might be inorder


This is a good place to start although not a tutorial:
http://support.microsoft.com/default...b;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in
Microsoft(R)
Excel 97
File Name: WE1183.EXE
File Size: 58041 bytes
File Date: 06/20/97
Keywords: kbfile kbappnote
Description: This Application Note can help you learn techniques for
writing
Visual Basic(R) for Applications code to customize menus in
Microsoft
Excel
97. This Application Note contains code examples that you can use
with
the
following elements: menu bars, menus, menu items, submenus, and
shortcut
menus.

http://msdn.microsoft.com/library/of...97/web/008.htm
MS Officer 97 Programmer's Guide
Chapter 8: Menus and Toolbars

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm



http://support.microsoft.com/default...02&Product=xlw
How to customize menus and menu bars in Excel

http://support.microsoft.com/?id=159619
XL97: Sample Macros for Customizing Menus and Submenus

http://support.microsoft.com/?id=213550
XL2000: Sample Macros for Customizing Menus and Submenus

--
Regards,
Tom Ogilvy


"TK" wrote in message
...

Thanks Tom

I think I need more of a stick by stick procedure. Say for example
I wanted to customize Worksheet Menu Bar, I think I choose
View/Toolbars/customize then Worksheet Menu Bar then new, delete
the controls not needed and save it as "NewMenu" for example.
The next step:
I assume it is loaded in the Workbook Open and deleted in the
Workbook
close sommthing like?
Private Sub Workbook_Open()
Application.CommandBars("NewMenu").Visible = True
End Sub

Now will the original menus load the next time Excel loads if
this
workbook
is not opened?

Thanks
TK




"Tom Ogilvy" wrote:

If you are customizing the existing worksheet menu bar, then
just
don't
remove the controls you want to retain.

If you are building new "worksheet menu bar", then just add the
standard
controls you want to use.

--
Regards,
Tom Ogilvy

"TK" wrote in message
...
Hi:
In an effort to make a workbook less intimidating
I would like to make invisible (actually removed not
dimmed) all Toolbars including the Worksheet Menu Bar
and Formula Bar when a workbook is opened then display
a customized Worksheet Menu Bar and Standard Menu Bar.
At the workbook close I world like to restore (not reset)
the users menu setup.
I know how to add and delete Worksheet Menu Items and
create toolbars with VBA but isn't there a way to use or
retain
and use the basics such as File/save and Tools/Spelling
without
writing code or macros for each one.

Thanks
TK