Allowing ONLY ONE Excel app instance ?

MRJOHN

New Member
Joined
Sep 30, 2006
Messages
1
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.

:pray:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
L

Legacy 98055

Guest
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,671
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,550
Members
431,885
Latest member
Rsdg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top