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.
Code:
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.