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!

 

[DG_Montana]

Your elegant solution using Application.OnTime worked like a charm with the modal form in the Windows environment. However, on the mac, the .Show method of the form continues to halt all processes until the form is closed. Oddly, even when I experimented with Application.Wait, the code execution in the Macro() procedure didn't begin until the Test() procedure had completed. It seems that the mac OS makes sure that only one thing happens at a time, no matter what. Here's what I tried.....

Application.OnTime Now + TimeSerial(0, 0, 1), "Macro"
Application.Wait(Now + TimeValue("0:00:20"))
With UserForm1
.Label1.Caption = ""
.Label1.WordWrap = False
.Label1.AutoSize = True
.Label1.Font.Bold = True
'show the Macro's progress on a modal form.
.Show
End With

Thanks again for all the great ideas. Unfortunately, I think the mac OS has thwarted us at every turn.

 

[Jaafar Tribak]

Nothing seems to work in Mac. Did they design it to make it that way ?

Well, this is last thing I can think of : Use a VBScript to run the code from.
It is a tacky approach and certainly more difficult to adapt your code around it but if the following example works on your Mac you may want to give it a try.


Example:

1- Again add a Label (Label1) to the UserForm (UserForm1) and place the following code in the UserForm module :

Private CurLoop As Long
Private Const MaxLoops As Long = 10

Private Sub UserForm_Activate()

    With Me
        .Label1.Caption = ""
        .Label1.WordWrap = False
        .Label1.AutoSize = True
        .Label1.Font.Bold = True
    End With
    CurLoop = 1
    Do
        DoEvents
        Me.Label1.Caption = "Processing Cell :   " & _
        CurLoop & " / " & MaxLoops
        delay 1
        CurLoop = CurLoop + 1
        If CurLoop > MaxLoops Then Exit Do
    Loop
    Unload Me

End Sub

Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)

    If CurLoop < MaxLoops Then Cancel = True
 
End Sub

Private Sub delay(t As Single)

    Dim i As Single

    i = Timer
    Do
        DoEvents
    Loop Until Timer - i >= t

End Sub

2- Put in a Standard module and run the Test Macro :

Sub Test()
 
    RunVBS
    
    UserForm1.Show
    
    Kill Environ("Temp") & "\Temp.vbs"

End Sub


Sub RunVBS()

    Open Environ("Temp") & "\Temp.vbs" For Output As #1
        Print #1, "Dim oWb"
        Print #1, "Dim i"
        Print #1, "Const MaxLoops=10"
        Print #1, "On Error Resume Next"
        Print #1, "i=i+1"
        Print #1, _
        "Set oWb = Getobject(""" & ThisWorkbook.FullName & """)"
        Print #1, "oWb.ActiveSheet.cells(i,1).Select"
        Print #1, "Do"
        Print #1, "oWb.ActiveSheet.cells(i,1)=i"
        Print #1, "WScript.Sleep 1000"
        Print #1, "i=i+1"
        Print #1, "Loop Until i>MaxLoops"
        Print #1, "oWb.ActiveSheet.Columns(1).ClearContents"
        Print #1, "Set oWb = Nothing"
    Close #1
    
    Shell "WScript.exe " & Environ("Temp") & "\Temp.vbs"

End Sub

 

[SomeKindaGreekGod]

Your elegant solution using Application.OnTime worked like a charm with the modal form in the Windows environment. However, on the mac, the .Show method of the form continues to halt all processes until the form is closed. Oddly, even when I experimented with Application.Wait, the code execution in the Macro() procedure didn't begin until the Test() procedure had completed. It seems that the mac OS makes sure that only one thing happens at a time, no matter what.

Thanks again for all the great ideas. Unfortunately, I think the mac OS has thwarted us at every turn.

I too have been searching an answer to this. Frustrating that I would search for so long before finding the answer ("Modeless doesn't work on Mac").
However, it is possible to have a progress bar form, I found, if you use the technique Microsoft shows in their knowledgebase article:
How to display a progress bar with a user form in Excel

Essentially, you put any code or function you need to run inside of the UserForm_Activate() routine, and within that you can put your form.Hide or Unload form call.

But everything else I've tried where you show a form, do some stuff, then hide it, on the Mac all code execution is stopped until you exit the form.

I would love to know how you got the StatusBar to work though. That's what sent me on this journey in the first place, because despite what I tried (DoEvents galore, etc), if ScreenUpdating was turned off, the StatusBar will not update until all code is done running.

 

[DG_Montana]

I ended up handling the mac "progress updates" in a totally different way than the pc. Essentially, in the mac environment, I create a blank workbook to display status updates. When a milestone in the process is reached, I activate the status workbook, turn screen updates back on, make it display the correct message using DoEvents, and then turn screen updates back off and continue processing. A stupid work around, but the only thing the mac os supported. Cell B5 in the status workbook is formatted to have a really big, colorful font that wraps, and a wide column width, so the mac user can't miss the new text on the screen. Here's a code snippet....

If Application.OperatingSystem Like "*Mac*" Then

' Activate the workbook containing the status sheet

tmpWorkbook.Activate

If SBText = "" Then
Application.StatusBar = False
Else
tmpWorkbook.Sheets(1).Range("B5").FormulaR1C1 = SBText
Application.StatusBar = SBText
End If

' Update the screen

Application.ScreenUpdating = True
DoEvents
Application.ScreenUpdating = False

... else do something more elegant using a modal userform for pc users....