Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    default menus (97 sr2)

    I have an application that works well except that sometimes the users will bypass the custom toolbar and use the default excel menu for functions like File - Exit or Edit - Copy/Paste...

    All the functions the users need is in the custom toolbar - how do I hide or disable the default menus? Also can I disabling the CTRL shortcuts for these menus too?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: default menus (97 sr2)

    You can remove the standard menubar from view:

    Application.Commandbars("Worsheet Menu Bar").visible=false

    FOr the rest:

    This file presents solutions to the problem of selectively 'killing' keys in Excel Applications. Some programmers want to prevent their users from typing in wrong key-combinations. Here are two different solutions that work good.

    Thanks Bob and Wassim for their contributions,

    Martien 74277,3540

    A) The Macro:

    This is a macro to kills all keys, so that you can now really control the input of the user. It is sent to me by Bob Umlas 104074,3375


    Sub KillKeys()
    'kills ALL keys combo!
    Dim Keys
    Keys = Array("", "+", "^", "%", "+^", "+%", "^%", "^%+")
    With Application
    For I = 1 To 8
    For J = 1 To 12
    .OnKey Keys(I) & "{" & "F" & J & "}", ""
    Next
    If I > 2 Then
    '"Normal" characters CAN be used!!
    For J = 33 To 148
    Select Case J
    Case 123 To 145, 37, 39, 40 To 43, 91 To 94
    Case Else
    .OnKey Keys(I) & Chr$(J), ""
    End Select
    Next
    End If
    Next
    End With
    End Sub


    [img]/forums/images/smilies/cool.gif[/img] The List

    If you want to be more selective in killing keys: here is a very complete list. This is a contribution made by
    Wassim 74164,3611

    Sub DisableExcelShortcutKeys()
    'Run to disable shortcut keys

    'Customize Function Keys
    Application.OnKey "{F1}", "" 'Displays On-Line Help
    Application.OnKey "+{F1}", "" 'What's This
    Application.OnKey "%{F1}", "" 'Insert a chart sheet
    Application.OnKey "%+{F1}", "" 'Insert a worksheet

    Application.OnKey "{F2}", "" 'Edit active cell
    Application.OnKey "+{F2}", "" 'Edit a cell comment
    Application.OnKey "%{F2}", "" 'Save As Command
    Application.OnKey "%+{F2}", "" 'Save Command

    Application.OnKey "{F3}", "" 'Paste number into formula
    Application.OnKey "+{F3}", "" 'Paste function into formula
    Application.OnKey "^{F3}", "" 'Define a Name
    Application.OnKey "^+{F3}", "" 'Create names from addresses

    Application.OnKey "{F4}", "" 'Repeat last action
    Application.OnKey "+{F4}", "" 'Repeat last find
    Application.OnKey "^{F4}", "" 'Close window
    Application.OnKey "%{F4}", "" 'Exit

    Application.OnKey "{F5}", "" 'Goto
    Application.OnKey "+{F5}", "" 'Find command
    Application.OnKey "^{F5}", "" 'Restore window size

    Application.OnKey "{F6}", "" 'Move to next pane
    Application.OnKey "+{F6}", "" 'Move to previouse pane
    Application.OnKey "^{F6}", "" 'Move to next Workbook window
    Application.OnKey "^+{F6}", "" 'Move to previouse Workbook window

    Application.OnKey "{F7}", "" 'Spelling command
    Application.OnKey "^{F7}", "" 'Move the window

    Application.OnKey "{F8}", "" 'Extend a selection
    Application.OnKey "+{F8}", "" 'Add to the selection
    Application.OnKey "^{F8}", "" 'Resize window
    Application.OnKey "%{F8}", "" 'Display macro dialog

    Application.OnKey "{F9}", "" 'Calculate all workbooks
    Application.OnKey "+{F9}", "" 'Calculate active worksheet
    Application.OnKey "^{F9}", "" 'Minimize thw workbook window

    Application.OnKey "{F10}", "" 'Activate menu bar
    Application.OnKey "+{F10}", "" 'Display a shortcut menu
    Application.OnKey "^{F10}", "" 'Restore workbook window

    Application.OnKey "{F11}", "" 'Create a chart
    Application.OnKey "+{F11}", "" 'Insert worksheet
    Application.OnKey "^{F11}", "" 'Insert Excel 4 macro sheet
    Application.OnKey "%{F11}", "" 'Activate VBE

    Application.OnKey "{F12}", "" 'Save As command
    Application.OnKey "+{F12}", "" 'Save command
    Application.OnKey "^{F12}", "" 'Open command
    Application.OnKey "%+{F12}", "" 'Print command

    'Customize Navigation Keys
    Application.OnKey "{DOWN}", "" 'Down arrow
    Application.OnKey "{END}", "" 'End
    Application.OnKey "{HOME}", "" 'Home
    Application.OnKey "{LEFT}", "" 'Left arrow
    Application.OnKey "{PGDN}", "" 'Page down
    Application.OnKey "{PGUP}", "" 'Page up
    Application.OnKey "{RIGHT}", "" 'Right arrow
    Application.OnKey "{UP}", "" 'Up arrow
    Application.OnKey "^{.}", "" 'Move within selection
    Application.OnKey "^%{LEFT}", "" 'Jump left between selections
    Application.OnKey "^%{RIGHT}", "" 'Jump right between selections

    'Customize File functions
    Application.OnKey "^{n}", "" 'New
    Application.OnKey "^{o}", "" 'Open
    Application.OnKey "^{p}", "" 'Print
    Application.OnKey "^{s}", "" 'Save

    'Customize Editing functions
    Application.OnKey "^{c}", "" 'Copy
    Application.OnKey "^{v}", "" 'Paste
    Application.OnKey "^{x}", "" 'Cut

    Application.OnKey "^{d}", "" 'Fill Down
    Application.OnKey "^{r}", "" 'Fill Right
    Application.OnKey "^{ENTER}", "" 'Fill Selection

    Application.OnKey "^{f}", "" 'Find
    Application.OnKey "^{g}", "" 'Goto
    Application.OnKey "^{h}", "" 'Replace

    Application.OnKey "^{y}", "" 'Repeat last action
    Application.OnKey "^{z}", "" 'Undo

    'Customize Insert functions
    Application.OnKey "^{k}", "" 'Insert hyperlink
    Application.OnKey "^+{+}", "" 'Insert blank cells

    'Customize Format functions
    Application.OnKey "^{1}", "" 'Format cells
    Application.OnKey "^+{~}", "" 'Apply general format
    Application.OnKey "^+{$}", "" 'Apply currancy format
    Application.OnKey "^+{%}", "" 'Apply percentage format
    Application.OnKey "^+{^}", "" 'Apply exponential format
    Application.OnKey "^+{#}", "" 'Apply date format
    Application.OnKey "^+{@}", "" 'Apply time format
    Application.OnKey "^+{!}", "" 'Apply Number format
    Application.OnKey "^+{&}", "" 'Apply Outline Border
    Application.OnKey "^+{_}", "" 'Remove Borders
    Application.OnKey "^{b}", "" 'Toggle Bold format
    Application.OnKey "^{i}", "" 'Toggle Italic format
    Application.OnKey "^{u}", "" 'Toggle Underline format
    Application.OnKey "^{5}", "" 'Toggle Strikethrough format
    Application.OnKey "^{9}", "" 'Hide rows
    Application.OnKey "^{0}", "" 'Hide columns
    Application.OnKey "^+{(}", "" 'Unhide rows
    Application.OnKey "^+{)}", "" 'Unhide columns

    'Customize Miscellaneous functions
    Application.OnKey "^{-}", "" 'Delete selection
    Application.OnKey "^{DELETE}", "" 'Delete til the end

    Application.OnKey "+{ENTER}", "" 'Complete and move up
    Application.OnKey "%{ENTER}", "" 'New Line in a cell

    Application.OnKey "{TAB}", "" 'Complete and move right
    Application.OnKey "+{TAB}", "" 'Complete and move left

    Application.OnKey "%{=}", "" 'Insert AutoSum Formula
    Application.OnKey "^{;}", "" 'Insert Date
    Application.OnKey "^+{:}", "" 'Insert Time
    Application.OnKey "^+{""}", "" 'Copy value from above
    Application.OnKey "^{'}", "" 'Toggle Display
    Application.OnKey "^{`}", "" 'Copy formula from above
    Application.OnKey "^{a}", "" 'Display formula pane
    Application.OnKey "^+{a}", "" 'Insert argument names

    'Customize Transition Navigation Keys
    With Application
    .TransitionMenuKey = ""
    .TransitionMenuKeyAction = xlExcelMenus
    .TransitionNavigKeys = False
    End With
    End Sub

    'Now if you want to enable these keys, and I think not all of them should be
    'disabled then you would need to exclude the last argument something like:

    'Application.OnKey "{F1}" will enable the F1 key.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: default menus (97 sr2)

    Wow! <img src=/S/joy.gif border=0 alt=joy width=23 height=23> I couldn't ask for a more complete answer to my question Jan!

    Well, actually I could... One thing you don't say is if these key disablements will persist beyond the closure of the workbook? ie: Will I need to restore them before exiting the application or are they restored when Excel starts up?

    Thanks so very much for the GREAT answer!!! <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/ribbon.gif border=0 alt=ribbon width=15 height=15> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: default menus (97 sr2)

    this is a small complement to the magnificent work sent by Jan.

    This code disable some options of the menu "Edit",
    - Cut, Copy, Paste, Especial Paste.

    the corresponding buttons and Keys.

    <font color=blue>Private Sub Workbook_Activate()
    ' Turn off the menu
    Application.CommandBars("Edit").Controls(3).Enable d = False
    Application.CommandBars("Edit").Controls(4).Enable d = False
    Application.CommandBars("Edit").Controls(5).Enable d = False
    Application.CommandBars("Edit").Controls(6).Enable d = False
    ' Turn off the toolbar:
    Application.CommandBars("Standard").Controls(7).En abled = False
    Application.CommandBars("Standard").Controls(8).En abled = False
    Application.CommandBars("Standard").Controls(9).En abled = False
    Application.CommandBars("Standard").Controls(10).E nabled = False
    ' turn off shortcutkeys:
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "^x", ""
    End Sub
    </font color=blue>

    <font color=red>
    And this code returns it.
    </font color=red>
    <font color=blue>
    Private Sub Workbook_Deactivate()
    ' Enable the menu:
    Application.CommandBars("Edit").Controls(3).Enable d = True
    Application.CommandBars("Edit").Controls(4).Enable d = True
    Application.CommandBars("Edit").Controls(5).Enable d = True
    Application.CommandBars("Edit").Controls(6).Enable d = True
    ' Enable the commandbar:
    Application.CommandBars("Standard").Controls(7).En abled = True
    Application.CommandBars("Standard").Controls(8).En abled = True
    Application.CommandBars("Standard").Controls(9).En abled = True
    Application.CommandBars("Standard").Controls(10).E nabled = True
    ' Enable the shortcut keys:
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "^x"
    End Sub

    </font color=blue>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: default menus (97 sr2)

    The disabling of keys is application wide and is forgotten once XL closes. Closing your application workbook will NOT reset the key (dis-) assignments though. So if your users can close your app and continue working in XL, you will need to reenable all keys from the thisworkbook, workbook_beforeclose event.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: default menus (97 sr2)

    I would use the activate and deactivate events since there are cases where the workbook will close without the close event firing. It also allows users to work in other worksheets while the application is running.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: default menus (97 sr2)

    So when would the beforeclose event NOT fire?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: default menus (97 sr2)

    It was about two years ago when I wrote the application that had this problem, but I believe that in XL97 it was if you clicked the close box in the upper right corner. There was also another time which I think happened when you got some dialog box and clicked on either No or Cancel.
    Legare Coleman

Posting Permissions

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