Dumb Macro Question

Hi Scott

Not a dumb question at all. What you need to do is attach the custom menu bar to the Workbook. You do this via View>Toolbars>Customize, select your menubar then click "Attach" and copy it to the Workbook.

Now you will need some code to delete the custom menubar when you close the Workbook. Don't worry this isn't as drastic as it sounds, it will automatically appear again when you re-open.

Place this code in the Workbook module. Right click on the sheet picture, top left next to "File"

Private Sub Workbook_Activate()
'Show the Custom toolbar
Application.CommandBars("mymenubar").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True

If Cancel = True Then IsClosed = False 'Changed their mind
End Sub

Private Sub Workbook_Deactivate()

On Error Resume Next 'In Case it's already gone.
If IsClosed = True Then 'Workbook is closing
Application.CommandBars("mymenubar").Delete
Else 'They have activated another Workbook
Application.CommandBars("mymenubar").Visible = True
End If

End Sub

Change "mymenubar" to the name you have.

This code will delete your menubar when you close the workbook, so it is not available to any other Workbook. If the user activates another Workbook when yours is open, it will hide your menubar.

This also prevents any changes that a user may try to make to your menubar from sticking, as soon as they close and re-open it will revert back to normal. This of course means that if you wish to make any changes, you will need to unattach the menubar first, make the changes, then attach again and save.

I also have some code on my website that will hide all other Excel menubars upon opening (except your custom one) and restore them on closing or deactivation. To see this code follow my link to my website and click "VBA Tips and Tricks"

Good luck

Dave

OzGrid Business Applications