Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Disabling Excel (Excel 2000)

    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!
    - Ricky

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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 <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=65675&page=8& view=collapsed&sb=5&o=0&vc=1#Post65675>description </A> (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!
    Gre

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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...
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disabling Excel (Excel 2000)

    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...
    - Ricky

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disabling Excel (Excel 2000)

    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 ?
    - Ricky

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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?
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disabling Excel (Excel 2000)

    -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.
    - Ricky

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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.
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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
    Gre

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling Excel (Excel 2000)

    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!

    <hr>An alternative is hiding the existing Worksheet Menu Bar and introducing a Custom Menu Bar on Window Activate. <hr>
    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
    <hr>Anything else; like entering data, navigation and printing reports can be accomplished within the application through userforms and macro buttons.<hr>
    and the overhead of a custom toolbar is not high and might even provide an ideal relocation site for some of those buttons.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •