Worksheet Menu Bar - Deleting - Distributed App. 3

[Pyrrhus]

What I want to do is to distribute an Excel app. and stop the user from fiddling with it. I want to get rid of the Worksheet Menu Bar, or at least the commands on it (File, Edit, etc.). However this needs to be attached to the app itself, not to Excel (mine or the user's). That is, I don't want it to affect the menu bar configuration for any other Excel applications. Can anyone help, please?

 

[Loomah]

Hi
This will disable all the controls on the worksheet menu bar

Sub lime()
Dim cntrl As CommandBarControl
For Each cntrl In CommandBars("worksheet menu bar").Controls
cntrl.Enabled = False
Next
End Sub

This one removes the bar...

Sub lime2()
CommandBars("worksheet menu bar").Enabled = True
End Sub

Use one or the other...

Add the code to the workbook Open and Activate events. Reverse it (...Enabled=True) in the workbook Deactivate and Close events. I'm not aware of any way to do this without using code at the moment.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

Hi again
You may have to add the Application object to the code ie

Application.CommandBars("worksheet menu bar").Enabled = False

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Pyrrhus]

Loomah,

Thanks for your help, once again (you may recall that you helped me once before with a text box / linked cell problem). Worth at least a star.

Your suggestion worked well. In fact it worked too well. I changed the set window sub (on open) and saved it as a copy. Unfortunately when I saved it, I also saved it to the original file (don't know how)! I'm now locked out of my application and, of course, my last backup is a day or two old.

Any idea how I can restore the menubar after the app opens up with menu bar = false, or any other way I can get back into development mode?

Thanks again.
:-(

 

[carrr]

Did the same thing to myself once when I was experimenting with code like Loomah provided you...you need to put code in your Workbook_BeforeClose event that simply sets .Enabled = True

 

[Pyrrhus]

Thanks Carrr. But I don’t think that would fix my problem. I need to get access to the app to do some editing, while still developing. Maybe the solution is to not include the “menu bar = false” code until full developed and tested and immediately before distributing the app. Unless there is someway to override the false status without having access to the menu bar?!

Thanks for your help.

%-)

 

[bandit600]

Pyrrhus,

I developed a workbook in Excel 97 for distribution a while back and created a custom toolbar with a single button with a blank face. It was only obvious that it was there if you hovered over it. Clicking on it gave you a menu option to enter a password, entering the correct password removed the custom toolbar.

This proved invaluable for maintenance as the workbook evolved after distribution. It wont stop everybody, but then there are usually ways around most things.

I have not used it in some time and have not tested it recently, it could probably do with some tweaking. In case its any use to you or anyone else, here is some of the VBA which you can adapt to suit your own workbook

Sub menuHide()
Set oldMbar = CommandBars.ActiveMenuBar
Set newMbar = CommandBars.Add(Name:="cbar1", Position:=msoBarTop, MenuBar:=True, Temporary:=True)
With newMbar
.Visible = True
.Protection = msoBarNoMove
.Protection = msoBarNoCustomize
End With
Set newMenu = CommandBars("cbar1&quot.Controls.Add(Type:=msoControlPopup)
Set ctrl1 = newMenu.CommandBar.Controls.Add(Type:=msoControlButton, Id:=2949)
With ctrl1
.Caption = "TrapDoor"
.TooltipText = "Original Menus"
.Style = msoButtonCaption
.OnAction = "removeCustomMenu"
End With

End Sub

Sub removeCustomMenu()
'
'
Dim Message, Title, Default, MyValue
Workbooks("Book1.xls&quot.Activate
Worksheets("Sheet1&quot.Activate
Message = " Please enter the password."
Title = "Password Required"
Default = "******"
MyValue = InputBox(Message, Title, Default)
If MyValue = "Correct" Then
Application.CommandBars("cbar1&quot.Visible = False
Else
'
End If

End Sub

bandit600

 

[Pyrrhus]

Hello bandit600,

Thanks a lot for going to the trouble. I can't quite figure out how it works right now, but I'll check it out as soon as.