Worksheet menu bar using code????

[Richard m]

I am trying to create a custom Main Menu for a spreadsheet.
I can install macro in the thisworksheet, workbook to edit th
Worksheet menu bar and when the spreadsheet opens, it will edit th
main menu. But...




- I have not been successfull in reseting the worksheet menu bar whe
the spreadsheet closes. How do you reset the Worksheet menu bar whe
you click the close box?
- Can a custom worksheet menu bar be created and attached to wor
with only one spreadsheet? And how?



Here is the code I am playing with to develop a custom menu. The secon
sub is not working.


Code
-------------------

Private Sub Workbook_Open()
Application.CommandBars("Tools").Controls(2).Delete

End Sub

Private Sub Application_WorkbookBeforeClose(ByVal playmenu As Workbook, _
Cancel As Boolean)
Application.CommandBars("Tools").Controls.Add Type:=msoControlButton, ID:= _
793, Before:=2



End Sub

 

[Dave Peterson]

If you're adding more options to the worksheet menu bar, you may want to look at
the way John Walkenbach did it in his menumaker program. It looks very neat and
is easy to update.
http://j-walk.com/ss/excel/tips/tip53.htm

It doesn't reset the menubar, it just cleans up after itself.

And I think the easiest way to disable your macros from other
worksheets/workbooks is to just a a bit of code to the top of each macro:

You could check for the path, workbook name, and worksheet name if you wanted.

if lcase(activeworkbook.fullname) _
= lcase("c:\my documents\excel\book1.xls") then
if lcase(activesheet.name) = lcase("MySheetName") then
'ok to run
else
msgbox "uh, uh, uh.
exit sub
end if

Put it in a function and just call it whenever you need to check.


Option Explicit
Function OkToRun(myWkbkName As String, mySheetName As String) As Boolean

OkToRun = False
If LCase(ActiveWorkbook.FullName) = LCase(myWkbkName) Then
If LCase(ActiveSheet.Name) = LCase(mySheetName) Then
OkToRun = True
End If
End If

End Function

Sub testme()

If OkToRun("c:\my documents\excel\book1.xls", "sheet1") Then
'do nothing special
Else
MsgBox "nope"
Exit Sub
End If

End Sub

You could also use the workbook_deactivate and _activate and
worksheet_deactivate and _activate to hide/show your menu (but I think this is
easier).

 

[Robert Rosenberg]

You can create your own custom toolbar (not worksheet menu bar) and attach
it to any workbook. Problem is the toolbar (just like the menu bar) does not
disappear when the workbook you attached it to is closed. A copy of the
toolbar remains and appears along with the other built-in toolbars. However,
you can use a macro to remove the toolbar whenever the workbook closes.

The simplest approach:

1. Open the workbook you want the toolbar to reside in (and make sure it's
the active one)

2. Use the Tools-->Customize command to create a new custom toolbar (New
button on the Toolbars tab)

3. Add the desired button(s)/commands to the new custom toolbar

4. If you need to attach any of them to macros, right-click the custom
button and use the "Assign Macro" command on the shortcut menu

5. When finished creating the toolbar, click the Toolbars tab

6. Click the "Attach" button and use the Copy button in the Attach dialog to
copy the new toolbar (on the left side of the dialog) into the active
workbook (right side of dialog)

7. Click OK, then close the Customize dialog

8. Acess the VBE (ALT+F11) and double-click the "ThisWorkbook" for the
workbook containing the custom toolbar.

9. Copy the routine below into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Custom 1").Delete
End Sub

Where "Custom 1" is the name of your custom toolbar

10. Save the workbook

When the workbook opens, so does the toolbar. It disappears (gets deleted)
whent he workbook closes.

Note: If you need to make changes to the toolbar, you'll need to re-copy the
updated toolbar into the workbook using steps

 

[Greg Wilson]

My read of your post was that you meant to delete and
restore the 2nd menu item of the Tools menu of the
Worksheet Menu Bar (WMB) as opposed to the second control
on a custom toolbar named "Tools". Your code refers to a
toolbar named "Tools".

I think you're looking for the following. I use the
Deactivate event to reset the WMB because the BeforeClose
event allows the user to cancel after the WMB has been
reset. I make the control invisible instead of deleting
it. Correct for workdwrap of the code.

Private Sub Workbook_Open()
Application.CommandBars(1).Controls("Tools").Controls
(2).Visible = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars(1).Reset
End Sub

As for your second question: You can create a custom
toolbar that will only be visible when a certain worksheet
is selected by using the Worksheet_Activate and
Worksheet_Deactivate events as follows. Place the code in
the code module pertaining to the worksheet involved.

Private Sub Worksheet_Activate()
CommandBars("Data").Visible = True
End Sub

Worksheet_Deactivate()
CommandBars("Data").Visible = False
End Sub

Hope it was what you were looking for.

Regards,
Greg

 

[Richard m]

Greg, I tried the samples you posted and they worked great. I was abl
to pick and choose what I want to be visible.
The reset works.

I looked at the way John Walkenbach added menu and when downloadin
the xls all I got was an .exe file. Not sure why. but I found hte Ti
was interesting. I knew I was able to create a menu in Excel 4.0.

Thanks for all replied -- What a great forum