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!

 

[mikerickson]

You can't make a userform modal on a Mac.
The closest that one could come is a floating command bar.

DropDowns, EditBoxes (text boxes), and command butttons are standard commandbar controls.
The equivalent of a ListBox could be contrived from a series of command buttons.
Checkboxes and Option buttons = more command buttons.



BTW, you can't have more than one instance of Excel running at a time on a Mac.

 

[Jaafar Tribak]

You can't make a userform modal on a Mac.
The closest that one could come is a floating command bar.

Hi Mike.

Can you make API calls on Mac ?

 

[mikerickson]

No API.
Not unless you are running Windows, in which case, I don't know.

If I wanted a progress bar, I would either build one in AppleScript (how I'm not sure) and call that.
OR
I would make a shape and have the slow VB routine lengthen that shape at appropriate intervals.

Two VB routines running at the same time. (which is kind of what a non-modal UF is) doesn't fly.

 

[mikerickson]

Hmm... added thought, it would need testing but...
Make a shape with VBA.
Trigger an AppleScript with VBA and then start the slow VB routine.
Have the AppleScript monitor the VB routine (AS can read cells or Names, not vb variables) and reach into Excel and adjust the shape....or on a time schedule.

Also, let me mention conditional compilation

#If Mac Then
    Rem Mac code
#Else
    Rem Windows code
#End If

 

[Jaafar Tribak]

No.
Not unless you are running Windows, in which case, I don't know.

BTW, you can't have more than one instance of Excel running at a time on a Mac.

What annoying limitations ! This would castrate any ambitious developper .

Thanks for the info.

 

[mikerickson]

I prefer to think that it focuses one on the essentials.

(Since I can't change it, focused is better than annoyed )

 

[DG_Montana]

The lively discussion between Jafaar and Mike is very illuminating. And to clarify, the add-in is already working just fine on macs that are running Windows. The goal is to have the add-in working natively in Excel 2011 for the mac, so that mac users don't have to run Windows.

Since I don't know anything about AppleScript and have a publication deadline looming, I won't be attempting that work-around at this time. I did try the various commands suggested by Jafaar for starting a new instance of Excel on the mac, but simply ended up agreeing with Mike assertion that you can't have two instances of Excel running on the mac.

For now, I'll be using conditional statements to prevent the progress windows from being displayed to mac users. They'll have to settle for updates to the status bar, instead.

What's the best way to convey to Microsoft my disappointment with the lack of feature consistency between platforms?

 

[mikerickson]

Excel Help includes a Feedback option. You can add yours to the tall pile of comments about difficulties involved in cross-platform compatibility.

On the other hand much of this is not MicroSoft's fault. It's Apple's design of their OS that makes things difficult.

As I understand it, at the core of these problems is the fact that ActiveX controls present a security vulnerability. Window's solution is to recommend security programs, like Macafee, etc,. Apple's solution is to forbid ActiveX.

I can't argue with Apple's decision.
I'd find it hard to tell customers, "After you buy our computer, you must also buy another piece of software (from someone else) and continue to pay for their updates (at a changing price to be determined by them) if you want your computer to be safe from the scoundrels."

 

[RoryA]

Considering they've also only just put VBA back for the Intel Macs and gone from VBA5 to 6, perhaps modeless forms were just relegated for the time being. Maybe in the next version...

 

[Jaafar Tribak]

Just remembered that Ontime routines conviniently work (except when trying to activate other windows) even when a Modal userform is currently on display... So, make your data processing macro part of an OnTime routine and see if it works.

To illustrate what I mean ,here is a simple example :

Create a new userform (userform1) , put in it a lable Control (Label1) and run the following Test routine from a standard module .

Sub Test()
  
    Application.OnTime Now + TimeSerial(0, 0, 1), "Macro"
    
    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
    
End Sub

Sub Macro()

    Const lMaxLoops As Long = 20 '<-- change val as needed.
    Static i As Long
    Dim j As Long
    Dim oRange As Range
    
    Set oRange = Range("a1:a" & lMaxLoops)
    oRange(1).Select
    i = i + 1
    j = ((i - 1) Mod 3)
    Select Case True
        Case UserForms.Count = 0, i > lMaxLoops
            i = 0
            oRange.ClearContents
            Unload UserForm1
        Case Else
            UserForm1.Label1.Caption = _
            "Processing Cell " & String(j + 1, ".") & _
            String(3 - j, "  ") & "   " & i & " / " & lMaxLoops
            oRange(i) = i
            Application.OnTime Now + TimeSerial(0, 0, 1), "Macro"
    End Select

End Sub

Hopefully, you can adapt this approach to meet your specific needs.