Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suppress File Open (XP; SP3)

    What is the best VBA approach to suppress the file open options in Excel?

    I have a worksheet available to users that I would like to prevent them from using the dropdown "File>Open" and clicking the "Open Folder" icon.

    John

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

    Re: Suppress File Open (XP; SP3)

    You can put code into the Workbook Activate event routine to remove the Open command from the File menu, and code in the Workbook Deactivate routine to put Open back on the File menu. However, this will not prevent them from opening another workbook before opening your workbook, or going to Explorer and opend a file there.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suppress File Open (XP; SP3)

    In addition to Legare's comments, you would probably also need to use application.onkey to disable the "ctrl-o".

    <pre>Application.OnKey "^o", ""</pre>


    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suppress File Open (XP; SP3)

    John Walkenbach's site has some code (written by Bill Manville) which enables and disables the copy and paste functions which could be adapted to the task.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress File Open (XP; SP3)

    Steve,

    Thanks for the link. I tried a simple test of the "EnableControl" code and I came up with a compile error message: "sub or function not defined".

    Sample code:
    Sub Test_Open_Control()
    EnableControl 23, False
    End Sub


    As far as I can tell it should work. I don't believe there are any references needed.

    John

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Suppress File Open (XP; SP3)

    Did you copy the code named "EnableControl" from the link into your module?

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress File Open (XP; SP3)

    That would be the problem... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    John

Posting Permissions

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