ShowModal equivalent on mac

[DG_Montana]

I have an Excel Add-in written in VBA that works in Excel 2003, 2007 and 2010. It contains two forms that are essentially progress windows. The forms have their ShowModal property set to False and receive updates as long-running macro processes continue to execute while the forms remain on the screen to inform the user of the macro's progress.

I've been asked to make this add-in work in Excel 2011 for the mac. During my testing on the mac, nearly all processes work correctly except for the display of the progress windows. There is apparently no equivalent to the ShowModal property in Excel 2011 on the mac - or none that I can find...

When I run the add-in in debug mode in Excel 2011, macro execution simply stops when the form that I'd like to use as a progress window is displayed. Once I close the form, macro execution continues normally. If anyone has had success using a form as a progress window in Excel 2011, please, please tell me how you managed it!

Many thanks!

 

[shg]

Hi, DG, welcome to the forum.

I've never used a Mac, but a less-sexy alternative is to use the status bar.

 

[Jaafar Tribak]

I've never used Mac either but you could try forcing a Modal userfom into a Modeless one by using a simple call to the ShowWindow API.

1- Put this code into your UserForm Module :

Option Explicit
 
Private Declare Function ShowWindow Lib "user32.dll" ( _
     ByVal hwnd As Long, _
     ByVal nCmdShow As Long) As Long
 
Private Declare Function FindWindow Lib "user32.dll" _
    Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
 
Public Sub ShowModeless()
 
    With Me
        .Left = (Application.Width - .Width) / 2
        .Top = (Application.Height - .Height) / 2
    End With
    ShowWindow FindWindow(vbNullString, Me.Caption), 1
 
End Sub

2- Now instead of calling the userform with the native Show Method you call it like this :

Sub CallUserForm()
    
    UserForm1.ShowModeless

End Sub

I used this technique for userforms in Excel 97 where Modeless userforms were not introduced yet.

 

[DG_Montana]

Thanks for the warm welcome to the forum, shg.

My add-in already uses status bar messages, and those are working fine on the mac. The client really wants the progress windows to work, as well.

I copied the code suggested by Jaafar into one of the forms in question, and to my astonishment it compiled. However, when I ran the code, it failed to find "user32.dll". The exact error message is "File not found: user32.dll".

I copied the file in question from my Windows PC to the mac (putting it in the same location as the add-in file) and tried again with the same result. I also tried to point to the dll in Tools-References, but the mac VB editor doesn't seem to know what to do with a dll file.

The following post seems to have some relevance, but I must admit that I've never done an API call and seem to be in over my head...

http://stackoverflow.com/questions/1314645/equivalent-of-user32-dll-on-os-x

 

[shg]

I think Jaafar's suggestion is very clever, but don't have a clue how to make it work on a Mac. There are some smart Mac people here, though, and maybe one of them can help.

 

[RoryA]

You cannot use Windows API calls on a Mac (unless running windows on it of course).

 

[DG_Montana]

It appears that I'm not the only one who has run into this problem. The developer in the post below seems to have given up, though.

http://forums.techguy.org/business-applications/1017872-modeless-userform-mac-excel-2011-a.html

If there is a mac guru on this forum who can give me some direction about making an API call to the mac equivalent of user32.dll, I think Jaafar's suggestion looks promising. I'm just not experienced enough on the mac to get it implemented properly....

Thanks!

 

[Jaafar Tribak]

I have no idea how Mac users program their OS ... I have done a quick search but nothing came up.

@ DG_Montana

One possiblility to achieve what you want is to call the userform from a seperate hidden instance of excel created on the fly. It is not difficult to do but you will need to adapt your code and pass a pointer of the workbook to the second instance.

 

[DG_Montana]

Thanks for the suggestion about creating a new instance of Excel to display progress window. Unfortunately, I'm stuck in the implementation of creating a new instance of Excel on the mac....

My code is doing the following:

Dim XlApp as Excel.Application
Set XlApp = New Excel.Application

On the "set" statement above , I get an error that says "ActiveX component can't create object". All my searches on this error statement suggest looking at various Windows .dll files, making sure that things are correct in the registry, and other stuff that doesn't exist in macLand.

Any and all suggestions welcome!

 

[Jaafar Tribak]

Thanks for the suggestion about creating a new instance of Excel to display progress window. Unfortunately, I'm stuck in the implementation of creating a new instance of Excel on the mac....

My code is doing the following:

Dim XlApp as Excel.Application
Set XlApp = New Excel.Application

On the "set" statement above , I get an error that says "ActiveX component can't create object". All my searches on this error statement suggest looking at various Windows .dll files, making sure that things are correct in the registry, and other stuff that doesn't exist in macLand.

Any and all suggestions welcome!

Well,I don't know anything about Programming in Mac but I would be surprised if one couldn't instanciate an instance of excel using the Set statement.

One could instanciate a new instance of excel via the Shell command in this scenario but this approach would also require a call to the CopyMemory API which unfortunatly woudn't work on a Mac.

Anyway, did you try ?

Set XlApp = New Application

Or

Set XlApp = CreateObject("Excel.Application")