Okay, I have a solution. This is a fairly long description of the problem and solution to provide enough detail for someone else to implement it, should they wish.
It's based on something I saw VAX VMS programmers do years ago. Lotsa years ago. There were things DCL (the console scripting language) could do and things it couldn't do. If I remember right, it had something to do with variable substitutions in scripts. To overcome limitations, they would write a script that would write another script that actually did the job. They called it "two deep" and some of us called it "too deep." Anyway, I think one of them routinely wrote three deep.
Anyway, scripts creating scripts was routine. So I devised a Mac VBA solution that mirrors that.
Here's the problem: as I mentioned in my first post, it's an issue of early vs late binding. If the userform is defined when you write the code, you can use its properties normally, like
In Windows' Excel you can define it after you write the code and using the proper command, run it by name, as in the following:
On Mac, that functionality does not work. Even if the userform will be created by the time the code is run, "myUserForm.Show" will not compile when you enter it if it doesn't already exist.
So, the solution is to write a script that refers to AFTER it's created. That describes what I devised. I used the John Walkenbach (https://www.thespreadsheetguru.com/t...lete-userforms) routine as a starting point and added four others by Chip Pearson (http://www.cpearson.com/Excel/VBE.aspx) which add and remove both code modules and subroutines.
Did I need to do this? Perhaps not, but I have had clients ask questions about it (creating forms on the fly based on needs) and I wanted to prove it could be done. It can, but is it worth it? I'm not sure yet. There are several problems.
1) There is a known bug in Excel VBA that complicates creating a userform programmatically: that of giving it your desired name. It seems to work fine the first time, but after that it is erratic. What happens is that your form is created with the name UserformX where X is a sequence number assigned at create time, and when you try to rename it to your preferred name, it throws an error. According to what I've read, the only sure solution is to shut down Excel and start it back up again, and then your rename will be effective. But subsequent attempts may have the same difficulty. My code tells the create routine what name I want to use, but to tell the calling routine what name it actually used. That means the calling routine knows what the form name is but subsequent programs won't. Yuk.
2) If the VB editor is active and has windows open, there can be flickering as the userform window and code windows are built/activated. The solution is to hide the VBE while this process runs, using segments in the original Walkenbach routine (which are omitted from this code for brevity's sake).
3) After the process is over, if the VBE is active, those windows will be visible again. If you don't want those cluttering the workspace, you need to make them not visible, another VBE call.
4) In light of 2) and 3), it really speeds up the operation if you don't have the VB editor active while creating and running a form in this fashion.
5) For a simple form such as this one, the supporting code for the controls is simple. For more complex forms with numerous controls, the code gets complicated and lengthy. You can compensate by putting the routines that do the actual work in the main code module(s) and call them from the controls' events.
6) If this process is so complicated, why bother? Just create the userform in Windows (since Excel for Mac after v2011 does not have a userform editor) and migrate it to your Mac.
But if, after all that, you still want/need to do it, the code in the attached workbook shows how. To demonstrate it, open the workbook and click the button on the worksheet that says (... wait for it ...), "Click me". It pops up a windows that resembles the original InputBox. Type something or cancel and a message box will report what you did.
One of the reasons I got started down this path is that pixels on Mac are smaller than those on Windows, making the type rather hard to read. You will get eye- or neck-strain if you don't do something about that. I implemented a solution devised by Ron de Bruin (https://www.rondebruin.nl/mac/mac022.htm) that enlarges the form, controls and fonts on the fly. So I wrote alternative userforms for InputBox and MsgBox that call the enlarge routine on initialization. I also can control the background color and the font style, button placement and indeed add more buttons if I want. My clients love it but portability can be an issue. So, this solution might help me out when it comes up again.
You are free to use the code, since I can't claim ownership, and the folks I've named above seem happy to share.
Good luck, and if you'd like more information, contact me via this forum.
Chuck L
Bookmarks