asked on

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.

calacuccia

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

ASKER CERTIFIED SOLUTION

calacuccia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dhadland

ASKER

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