Menus

[Guest]

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.

 

[Guest]

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]

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.aspx?scid=kb;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/officedev/office97/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.aspx?scid=kb;en-us;830502&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

 

[Guest]

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]

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/d.../en-us/odeopg/html/deovrcopyingcommandbar.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

 

[Guest]

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]

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.

 

[Guest]

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 (e-mail address removed) and we can arrange a
mutually convenience time to discus it.

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

 

[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

 

[Ron de Bruin]

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

 

[Guest]

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