Dutch Gemini's Weblog Pages

February 19, 2009

Weird bug in Excel’s Application.GetOpenFileName() function

Filed under: Excel,Programming,VBA — dutchgemini @ 12:48 pm

In an application I’m developing in Excel 2002/XP I need to open a file. Because I wanted to keep the external references and components to a minimum, I decided to go for Excel’s native Application.GetOpenFileName() function because it provided all the necessary functions and features I was looking for and because I told myself, if it is part of Excel then it gotta be fine.

However, during the test phase I bumped into a weird problem: while I was showing a modal dialogue form, all of a sudden I could click and select ranges of cells on the worksheet but also click all the menus and command bar buttons of Excel.

Sitting on top of my consolidated programming experience I was shocked: how could it be possible that I could click outside a modal dialogue form as if I was running a modeless dialogue form. I scanned the code over and over, looked in the form’s properties, debugged step-wise with all the watches on but nothing, no sign of a modeless call. It had to be somewhere else.

Then, by coincidence, the answer: as long as I did not activate the open file function, the form behaved modal. As soon as I activated the open file function, the form became modeless.

To replicate this behaviour do the following:

  1. In a virgin workbook add a Userform with a single CommandButton
  2. In the button’s Click event Dim a variable of type Variant
  3. In the same event procedure add this statement

    vVariable = Application.GetOpenFileName()

  4. Via code issue a Userform.Show vbModal. This will popup the form. Any click outside the form’s area will result in a ‘bump’ (being modal this is exactly what you expect).
  5. Click the button and when the Open dialogue comes up. Again, any click outside the Open dialogue’s area will result in a ‘bump’ (being this one also modal this is exactly what you expect).
  6. Click Cancel. You can now click outside the form’s area and select cells, click menus, etc.:  your modal Userform has now become modeless!

Is this another of those undocumented feature of Excel? Or does it qualify as a bug?

I have noticed the same weird behaviour to occur on the equivalent Application.GetSaveAsFilename(), and I do not want to dare guessing how many of the other internal dialogues have this quirk.

Luckily there is a solution and a workaround that prevents Excel from transforming a modal into a modeless Userform, something I was avoiding from the beginning:

  • do not use GetOpenFileName() when working from within modal Userforms, but
  • use the Microsoft Common Dialog Control instead. This control respects the ShowModal setting of the hosting Userform, including the ZOrder.
I have not yet tested more recent versions of Excel, the bug(?) may have been solved in the mean time. I decided to modify the code, since the application must run on Excel XP as well.
Advertisements

3 Comments »

  1. Hi,

    I just came upon this post while searching Google… I can confirm this bug for Excel 2007. Good idea to circumvent this problem using Windows API, thanks!

    Daniel

    Comment by Daniel — August 23, 2009 @ 9:42 pm | Reply

  2. I have developed a solution for tackling the loss of modal state that currently runs in Excel without problems.

    Using 2 Win32 API functions I read the current state of Excel and if it has changed, restore it right after calling the GetOpenFileName -or whatever other Excel or COM Automation- function. Works on Excel 2002 and higher (because previous versions do not have the Application.Hwnd property).

    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
    Dim bExcelState As Boolean

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

    ' call the external or internal function
    '
    {...}

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

    No guarantee of any kind, but until today I have not had any consequential damage.

    Dutch

    Comment by dutchgemini — October 13, 2009 @ 3:34 pm | Reply

  3. @Dutch ==> thanks for the workaround.

    Comment by Dave — October 26, 2009 @ 7:21 pm | Reply


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: