• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

Excel - VBA - Prevent files being opened into Excel Session

I have developed an integrated excel workbook with customised toolbars.  When the workbook opens it runs code to hide all microsoft toolbars and opens the customised toolbars.

I would like to be able to prevent users of the spreadsheet opening any other files while the spreadsheet is open (eg if a user double clicks on an excel file in explorer it would normally open the excel file into the current Excel session - is it possible to have this file redirected into another session of excel - ie the user would see the normal excel workspace or at least prevent the file being opened.
0
dhadland
Asked:
dhadland
  • 2
1 Solution
 
calacucciaCommented:
Hi dhadland,

In the workbook specified you have to do following things:

1) Add a class module (right click on project in porject tree, Inert/Class Module)

2) In the class module, called Class1 by default, insert this code, it will close the opened workbook straight away.

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
ActiveWorkbook.Close
End Sub

3) In any of your normal modules, enter this code:

Dim X As New Class1

Sub InitializeApp()
    Set X.App = Application
End Sub

4) In the list of code runned on the workbook open event (the workbook you specified above), call the InitializeApp macro, just adding the line

InitializeApp

in one of the macros which runs on opening the workbook


This is sufficient to prevent the user from opening other files or from within Excel, or from the Explorer. It's necessary to run the InitializeApp macro to activate the application defined as App.

Now how to open another Excel instance, I'm not sure yet. I'm well acquinted with Excel VBA, but not if going outside.

Good Luck

Calacuccia
0
 
calacucciaCommented:
Apologies for the typing errors.
0
 
dhadlandAuthor Commented:
Calcuccia

Thanks for help.

Comment -  the code closed files inlcuding the one the code is in - so added an IF statement to the -

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
     ActiveWorkbook.Close
End Sub

to exclude the main  file.

dhadland
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now