Disabling Excel (Excel 2000)

Topic

New Reply

Before I distribute a project, I would like to make a few things happen. Before I spend hours trying, perhaps someone could just let me know if it’s possible:

1. Using VBA, can I disable or remove toolbars AND prevent the user from adding them back. Reason: I know how to remove scroll bars, headers, etc, but what good is that if the user can manipulate the options??

2. Using VBA, can I remove elements from the excel menu? Really, all I need to remain is the ‘File’ menu so the user can Save, SaveAS and Exit. The program has it’s own help macros on screen and all print settings have been pre-defined, saved as macro buttons on screen.

Basically, I am just trying to keep the user from tinkering with the program – they should not be able to change the options or fonts or anything else. I have already placed code to intercept double-clicks, right-clicks and prevention from adding sheets. And in a book, I just found out how to diable Alt+F4. I’m sending the finished product to 75 restaurant managers who WILL mess things up unless I can make it bullet-proof!

Reply | Quote

Replies

1. The short answer is “Yes”. There is a fully worked example – code included – in Chapter 9 of Microsoft Excel 97 Developer’s Handbook. (As it runs on Excel 2000, it may also be in the 2000 Developer’s Handbook.) In simple terms, you need to set up a Class Module to capture your user’s existing settings – and to restore them once they exit your app. You may wish to consider whether you want any keyboard shortcuts (not already disabled by password protection) to be disabled as well!

2. The answer again is “Yes”. It is the same routine in principle as editing your Shortcut Menus – see this description (and attachment). You can customise your menu to your heart’s content. Personally, I would recommend adding macro buttons for your Save/SaveAs/Exit commands – unless you can find a way to lock down the right-click functionality at the top of the Worksheet Menu Bar (which will enable toolbars & customising to be brought back.) BTW make sure your Auto_Open closes all running versions of Excel first!

Reply | Quote

Ricky,

In similar circumstances, I decided that the best way would be to hide all the toolbars, then display my custom toolbar only. I had problems similar to yours because people would print reports using the dropdown File menu instead of using controls I provided which set the pagesetup options. Six locations had six sets of reports – all of which looked different!

Anyway, I am posting some of the code in a text file. Some of it may be unusable to you, but it might help you get the idea about how I approached the issue. The code for creating the toolbar in VBA is fairly lengthy…I am sure some here could make it much smaller. Some of the code in the text file is commented out because I am not quite sure I am finished with some of the controls in the toolbar.

Anyway…

Reply | Quote

Thanks Unka and Mike.

At least I know that my ideas are do-able. I have printed out the text file and have immediately seen parts I can understand and implement.

One of the things I forgot to ask was about the possibility of not allowing a second excel file to be opened as long as mine remained open. At the same time, not allowing my file to be opened if any other excel file were already open.

I would want my program to have exclusive “use” of the special toolbar, limited menu…

Reply | Quote

The following should help you:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.IgnoreRemoteRequests = False
Application.CommandBars(“Worksheet Menu Bar”).Enabled = True
End Sub

Private Sub Workbook_Open()
If Application.Workbooks.Count > 1 Then
MsgBox “you can only open this file if no other workbooks are open”
ThisWorkbook.Close
End If
Application.IgnoreRemoteRequests = True
Application.CommandBars(“Worksheet Menu Bar”).Enabled = False
End Sub

Whilst you have this file open, another file double clicked from windows explorer will still open, allbeit in another instance of excel and this new instance should open without the modifications to the menus.

Reply | Quote

Thanks Brooke –

When I start excel by double-clicking the given file icon, it always automatically starts up a workbook1 AND the given file(workbook). Since the code is set to prevent my file from opening if another is already open, should there be a quick instruction inserted that will close workbook1 ?

Reply | Quote

That would be a sure way to get me to avoid using your application. Why not use the Window Activate and Deactivate event routines to build your menus and restore the default menus?

Reply | Quote

–Why not use the Window Activate and Deactivate event routines to build your menus and restore the default menus? – Can you explain that further?

I’m thinking I would be doing them a better service by taking away as much of the excel functionality as possible. They would enjoy the program more if they knew they couldn’t do something wrong. I just want to PREVENT problems so I don’t have to REAPIR them later. The end user should only be able to open the file, save the file, save as (for backups), and exit the file. Anything else; like entering data, navigation and printing reports can be accomplished within the application through userforms and macro buttons.

The more features of excel that I can eliminate while the application is running the less likely they are to need help later.

Reply | Quote

By using the Window Activate and Deactivate event routines, you can do that while they are in your application, but not affect the functionality of Excel while they are working on anything else.

Reply | Quote

Legare,
On the whole I would agree with you. The only problem is being able to switch back to other open workbooks if the main workbook menu is hidden – that would need the file list and I’m not sure how to call that. However….

Ricky,
The following code will attach the more windows dialog to a custom button. you may want to play with the faceid – try 142,566 or 1548 for starters.

With Application.CommandBars(“Custom 1”).Controls
With .Add(Type:=msoControlButton, ID:=830)
.FaceId = 263
.TooltipText = “Other Files”
End With
End With

Reply | Quote

For switching windows, Ctrl + Tab will still cycle through all open Excel windows – unless that has been disabled by code.

If you customise the Worksheet Menu Bar on the Window Activate event, you will either need to store any customisations the user may have made – for reinstatement on the WIndow Deactivate event – or have the user treat that as a cost of using your application (by using .Reset on Window Deactivate). An alternative is hiding the existing Worksheet Menu Bar and introducing a Custom Menu Bar on Window Activate.

HTH

Reply | Quote

You are right about ctrl tab, but whilst most people frequenting this forum probably know about ctrl tab there may be people using this app that don’t. In addition to this some people are keyboarders, others are mousers. The key thing here is – as both you and Legare have hinted at – user friendliness. I’m sure Ricky doesn’t want the end users cursing him under his breath every time they run his app!

[indent]

---

An alternative is hiding the existing Worksheet Menu Bar and introducing a Custom Menu Bar on Window Activate.

---

[/indent]I had assumed that this was a given but rereading, you’re right, it’s not. But the stated goal is to hide all toolbars as[indent]

---

Anything else; like entering data, navigation and printing reports can be accomplished within the application through userforms and macro buttons.

---

[/indent] and the overhead of a custom toolbar is not high and might even provide an ideal relocation site for some of those buttons.

Reply | Quote