Menus

[TK]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[TK]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

[TK]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
>

[TK]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > >
> >
> >
> >

[TK]

Hi Tom

Ron de Bruin is one of the menu people I was referring to, I
have been to his website and his approach using menu ID’s
rather than names is more dynamic. I’ll solicit him through
his web site to see if he is interested or if he does this type of
fee based worked.
I would also like to have the main workbook review and critiqued.
This is a commercial application that I’m updating from a dos 123
app to a windows excel app.
If you do this type of fee based consultation and or work post
back or email me at [email protected] and we can arrange a
mutually convenience time to discus it.

Thanks
TK


"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > >
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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
> > > >
> > >
> > >
> > >
>
>
>

[Ron de Bruin]

Hi Tom

> seemed to be doing "dictator" applications when he first started posting
That was fun Tom<g>

I post back in this thread if we have a solution for TK

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



"Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > >
>> > > > > 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" <[email protected]> wrote in message
>> > > > > > news:[email protected]...
>> > > > > > > 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
>> > >
>> >
>> >
>> >
>
>

[Ron de Bruin]

I send this tip to TK to only have certain options enabled in the worksheet menu bar
************************************************

Hi Thomas

Try something like this
Use the links that Tom posted to add your own menu controls.

Run the sub tester to test
Run the second sub to enabled everything

You can run the code in the Activate and deactivate event in the thisworkbook module

Sub Tester()
Dim IDnum As Variant
Dim N As Integer
Dim ctrl_1 As CommandBarControl
Dim ctrl_2 As CommandBarControl

For Each ctrl_1 In Application.CommandBars(1).controls
For Each ctrl_2 In ctrl_1.controls
On Error Resume Next
ctrl_2.Enabled = False
On Error Resume Next
Next ctrl_2
Next ctrl_1

IDnum = Array("18", "23", "106", "3", "748", "30255", "109", "30095", "752", _
"21", "19", "22", "755", "478", "1849", "313", "849", "850", "2")

For N = LBound(IDnum) To UBound(IDnum)
On Error Resume Next
Application.CommandBars(1).FindControl(ID:=IDnum(N), _
Recursive:=True).Enabled = True
On Error GoTo 0
Next N
Application.CommandBars("Toolbar List").Enabled = False
End Sub



Sub All_Controls_Enabled_True()
Dim ctrl_1 As CommandBarControl
Dim ctrl_2 As CommandBarControl
For Each ctrl_1 In Application.CommandBars(1).controls
For Each ctrl_2 In ctrl_1.controls
On Error Resume Next
ctrl_2.Enabled = True
On Error Resume Next
Next ctrl_2
Next ctrl_1
Application.CommandBars("Toolbar List").Enabled = True
End Sub

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



"Ron de Bruin" <[email protected]> wrote in message news:u%23fKElC%[email protected]...
> Hi Tom
>
>> seemed to be doing "dictator" applications when he first started posting
> That was fun Tom<g>
>
> I post back in this thread if we have a solution for TK
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> > > 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" <[email protected]> wrote in message
>>> > > > news:[email protected]...
>>> > > > >
>>> > > > > 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" <[email protected]> wrote in message
>>> > > > > > news:[email protected]...
>>> > > > > > > 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
>>> > >
>>> >
>>> >
>>> >
>>
>>
>
>

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > >
>> > > > > 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" <[email protected]> wrote in message
>> > > > > > news:[email protected]...
>> > > > > > > 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
>> > >
>> >
>> >
>> >
>
>

[TK]

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > 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" <[email protected]> wrote in message
> >> > > > news:[email protected]...
> >> > > > >
> >> > > > > 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" <[email protected]> wrote in message
> >> > > > > > news:[email protected]...
> >> > > > > > > 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
> >> > >
> >> >
> >> >
> >> >
> >
> >
>
>
>