Menus

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

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
>

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
> >
>
>
>

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
> > >
> >
> >
> >

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

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
>