Dutch Gemini's Weblog Pages

October 13, 2009

Excel looses ‘modal’ status of Userforms

Filed under: Excel,Programming,VBA,Windows — dutchgemini @ 4:54 pm

I am developing an Excel Add-In that uses modal and modeless Userforms. While testing the Add-In, at some point I noticed to my surprise I could all of a sudden click outside of my modal Userform and right into Excel, select ranges, click menus and command bar buttons, and so on.

I had bounced into this problem earlier when working with the Application.GetOpenFileName function (here) and blamed this function to be the guilty guy.

Now it happened again, after showing a modal window (from an automation server) on top of my modal Userform. From a quick test with the same automation server in VB, the modal state was maintained correctly so it had to be Excel. A little later I discovered that the main Excel window had restored its state right after closing the automation server while still running code in the modal Userform.

I presume that Excel, which I was explained is not a pure SDI o MDI, does not correctly trap the ‘modal window’ stack and restores its window state while other modal Userforms are still on screen.

I have found this workaround to tackle the problem:

In a module:

Public Declare Function EnableWindow Lib "user32" (ByVal hwnd As Long, ByVal fEnable As Long) As Long
Public Declare Function IsWindowEnabled Lib "user32" (ByVal hwnd As Long) As Long

Somewhere in my form (for instance the CommandButton1_Click() event)

Dim bExcelState As Boolean   ' This boolean stores the current status of the Excel Window

' Save the current state (returns False if disabled and being isolated by a modal dialog)
bExcelState = IsWindowEnabled(Application.hwnd)

' Call the external or internal function that creates and destroys a window.

' Test again. If Excel has become enabled, force disable.
If Not bExcelState And IsWindowEnabled(Application.hwnd) Then bExcelState = EnableWindow(Application.hwnd, False)

The above code works with Excel 2002 and higher.

If you still use Excel 2000 then you will need to use the Win32 API function FindWindow() to obtain the window handle:

This article is pretty exhaustive about using FindWindow() with Excel.



Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: