Allowing ONLY ONE Excel app instance ?

[MRJOHN]

i searched thru the forums but can't seem to find what i'm looking for.
i don't want the user to have more than one open application at a time as this would simply ruin my project.

i'm stack with this and would appreciate any helping hand.

ray:

 

[Legacy 98055]

Good morning MRJ.
Please offer more details. I cannot imagine writing code that would just shutdown existing applications and / or refuse to allow other instances to be opened. As a user, that would really be irritating. How would this ruin your project? For now, please explain the problem, not how you intend to solve it. There may be other ways to approach this.

 

[Jaafar Tribak]

Mr John,

I remember asking this same question a while ago. I first thought about running some code at XL startup that would enumerate all the top level windows\processes until it could find an already running XL application and shuts the new instance down.

A shorter and perhaps quicker solution was to use a named Mutex which is a kernel object that is excusively associated with the calling application until the latter is terminated.Thus, if another Process\Application tries to own\associate itself with the same Mutex, a error is generated which can be used to our advantage in this scenario.

In order to use this you will need to create an AddIn and run the code from its Workbook_Open Event handler so it fires everytime XL is launched without causing problems that would normally happen if the code was to be placed in the Personal.xls .

So create a workbook, place the following code in its ThisWorkBookclass module , save it as an XL addin and install it.

Option Explicit

Private Declare Function CreateMutex Lib "kernel32" _
Alias "CreateMutexA" (lpMutexAttributes As Any, _
ByVal bInitialOwner As Long, ByVal lpName As String) As Long

Private Declare Function ReleaseMutex Lib "kernel32" _
(ByVal hMutex As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long

Private Const ERROR_ALREADY_EXISTS = &HB7
Private lMutex As Long
Private bAppPrevInstance As Boolean


Property Let AppPrevInstance(ByVal vNewValue As Boolean)

    If vNewValue Then
        lMutex = CreateMutex(ByVal 0&, 1, Application.Caption)
        If Err.LastDllError = ERROR_ALREADY_EXISTS Then
            Application.Visible = False
            MsgBox "Sorry, Only one instance of Excel is allowed ! ", vbCritical
            Application.Quit
            GoTo CleanUp:
        End If
    End If
    Exit Property
CleanUp:
    ReleaseMutex lMutex
    CloseHandle lMutex

End Property


Private Sub Workbook_Open()

    Me.AppPrevInstance = True

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Me.AppPrevInstance = False
        
End Sub

funnily enough, VB as oposed to VBA, has a ready made built-in Boolean property that returns if an other app is already running which ca be used to solve the problem at hand.It is called AppPrevInstance that's why I have adopted this name for the custom Property in the above code.

Strictly speaking, the code does not prevent the second app to open. what it does is close it as soon as it is loaded.

What I I would have loved though,is to completely prevent the new app from loading and appearing in the first place.This would look smoother and more professional but that's just maybe taking this too far

Regards.