How to have only one instance of a workbook open at a time

[derekg]

I am writing an estimating tool for window repair. To avoid confused workers (some of whom are not too computer savvy) I would like to only allow one instance of the workbook to run at a time.
What VBA code would work for this.
Thanks
derekg

 

[BobUmlas]

Application.Mergeinstances = False

 

[Momentman]

Application.Mergeinstances = False

Does this work in versions before 2013?

 

[derekg]

Does this work in versions before 2013?

Seems to be working in Excel 2007, Will try on 2010 soon.

Thanks you BobUmlas!

derekg

 

[derekg]

The code does suppress a new instance of the spreadsheet, but,, When data in entered in the "first" instance and I try to open the workbook again I receive the warning:
"Window Estimatorv1.23.xlsm is already open, Reopening will cause any changes you made to be discarded. Do you want to reopen Window Estimatorv1.23.xlsm?"
At least my Techs will have to think twice. Is there a way to suppress the warning as well?

"Keep not thinking"

derekg

 

[GTO]

Greetings Derek,

Seems to be working in Excel 2007, Will try on 2010 soon.

Thanks you BobUmlas!

derekg

I am running in Excel2010, and do not seem to find Application.MergenInstances. From what little I read:

Application.MergeInstances Property (Excel)
Excel 2013 additional instance functionality - Microsoft Community

...it appears to me that this Property was not added until Excel 2013, and is related to a change in the way Excel "normally" works (i.e. - what we are used to); that being, that apparently Excel makes it a bit harder to open a second (third, so on...) workbook in a new instance of the application - if that makes sense.

That said, you said you ran this in Excel 2007?

The code does suppress a new instance of the spreadsheet, but,, When data in entered in the "first" instance and I try to open the workbook again I receive the warning:
"Window Estimatorv1.23.xlsm is already open, Reopening will cause any changes you made to be discarded. Do you want to reopen Window Estimatorv1.23.xlsm?"
At least my Techs will have to think twice. Is there a way to suppress the warning as well?

"Keep not thinking"

derekg

So... I hope I am not missing something, but exactly what do you mean by a "new instance of the spreadsheet"?

At least for me, the "...is already open. Reopening will cause..." warning is issued if I try and open the same (unsaved) workbook in the same instance of the application.

If I start a new instance of the application, and try and open a workbook that I already have open in another instance of the application, then I get the warning, "... is locked for editing..." and the Open as Read-Only, Notify, or Cancel buttons/choices.

I hope I worded all that sensibly?

Mark

 

[derekg]

Mark,
Yes, it does seem to work in 2007. I only want one active copy of this window estimating spreadsheet running at any one time. If 2 or more are open my techs get confused. It doesn't matter if different worksheets are open in this, or any additional instance of excel running.

derekg

 

[GTO]

Hi Derek,

Could you post the code?

Mark

 

[derekg]

GTO,
code posted by bob in post # 2

Originally Posted by BobUmlas
Application.Mergeinstances = False

 

[GTO]

Yes, it does seem to work in 2007...

I don't wish to seem insistent, but I have checked this Property out in MS documentation, and the first I see it is in Excel 2013.

See these links:
For 2007: Properties
For 2010: Properties
For 2013: Application Properties (Excel)

So, I was hoping that you would post the entire procedure's worth of code, for possible testing. In absence of that for the moment, is it possible that you have set error handling to 'On Error Resume Next' someplace before the line 'Application.MergeInstances = False' is executed?

Mark,
...I only want one active copy of this window estimating spreadsheet running at any one time. If 2 or more are open my techs get confused. It doesn't matter if different worksheets are open in this, or any additional instance of excel running.

derekg

Okay, as I understand it, you do not want a second person to open the workbook, if another person already has it open. Presuming the workbook is not a shared workbook, the user should already be getting the aforementioned "...is locked for editing..." warning. Are you saying that the users ignore this warning and open it read-only? and that you wish to block this?

Mark