Do not allow loading/saving vs Allow loading/saving through VBA SetOption helper

XLS Padlock
1

Hi All,

I want to disable people from opening a workbook 1) before running my application and 2) during application running.

Sometimes I need to open or save another wb using vba in the background and I understand I can do this using the SetOption helper.

However, with my current settings, it will not allow app to open if other wb is open before running my application, but when my app is already running, it allows any other wb to open.

Here are my settings:

image.png520×536 24.4 KB

image.png717×422 37.9 KB

image.png751×638 49.6 KB

Please advise.

Thanks

Dan

2

UPDATE:
I tried checking the “Do not allow loading/saving other workbooks (even with VBA code)” checkbox under Security tab.

Now my app won’t allow ANY other excel wb to open. This is good, sort of. However, the documentation says that if you put this in your code you can VBA save or create other workbooks and turn it off with VBA:

'*** enable other wb opening/manipulation
Dim XLSPadlock As Object
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
XLSPadlock.SetOption Option:=“2”, Value:=“0”
'***

and then when you want to disable saving/opening other workbooks, just do this:

'*** disable other wb opening again
XLSPadlock.SetOption Option:=“2”, Value:=“1”
'***

But I did an experiment to see why my wb crashes when I try to do a SaveAs, and I believe this code is not unlocking the ability to use other workbooks at all. I put two buttons to toggle this functionality:

Button 1 code:

Private Sub CommandButton2_Click()
'*** enable other wb opening/manipulation
Dim XLSPadlock As Object
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
XLSPadlock.SetOption Option:=“2”, Value:=“0”
'***
End Sub

Button 2 code:

Private Sub CommandButton3_Click()
'*** disable other wb opening/manipulation
Dim XLSPadlock As Object
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
XLSPadlock.SetOption Option:=“2”, Value:=“1”
'***
End Sub

Unfortunately, when I try to open another saved workbook (Whether before I press either buttons, after I press button 1 + close userform, or press button 2 + close userform), it says this regardless:

image.png1062×122 5.19 KB

Please help, I have a product launch very soon.

Thanks

Dan

3

Could this option be helpful in your case?

image.png743×636 47.9 KB

4

Hi @gdgsupport

In order to disallow other workbooks prior to and after my app is running, whilst being able to manipulate (save,open, etc) other workbooks via the app in VBA, how would you do it?

Also, are you suggesting I check or uncheck the highlighted checkbox?

Thanks

Dan

5

Turn it on.

6

It won’t open my secure App IF there’s already an instance of Excel running (which is good), however, it will allow other workbooks to be opened once my app is already running. Is there a way to do both?

Thanks

Dan

7

Normally yes, but in your specific case, it seems the two options don’t work together.