Enable VBA Support for creating and editing UserForms (not the Data Form)
In previous versions of Mac Excel you could create a Form/Dialog using VBA (called a VBA UserForm). In Mac Excel 2016, you can no longer do this. Please bring that functionality back. Note that this is different than the "Form" option on the Data menu. There's a separate suggestion on UserVoice for that.
(March-2018: MS updated the Title and Description to avoid confusion with the Data Form request)
Thanks for all your votes and comments about enabling support for UserForms in Excel for Mac.
With emphasis on enabling more and more capability using Office add-ins in Excel that will work across platforms, we don’t expect to add support for creating and modifying UserForms.
Please see this article for more information about what you can do in Excel with Office add-ins.
To Richard Brenner:
>>Hey, I just noticed VBE's main menu includes an Add-Ins item just like regular Workbook windows.
>I'm not seeing this. Can you provide a detailed description of its location?
Oops. The Windows VBE has Add-Ins in the main menu bar. I can't find Add-Ins in the Mac VBE anywhere. Sorry.
>>Why they would kill the graphical editors but leave all VBA's capabilities to create and edit
>>UserForms and PowerQueries intact is a mystery to me.
Maybe this should be raised as an antitrust issue - i.e., a predatory business practice designed to harm consumers who prefer the non-MS owned O/S ...
To Steve Van Dellen:
>Hey, I just noticed VBE's main menu includes an Add-Ins item just like regular Workbook windows.
I'm not seeing this. Can you provide a detailed description of its location?
< ... but they are working with 30 year old code ...
I thought that was the problem for a long time too but I suspect now disabling the UserForm editor on Mac is a policy decision rather than a technical problem mainly because they've done the same thing with Power Queries. Excel for Windows includes a very nice, graphical Power Queries editor. Excel for Mac has been able to run Power Queries created on Windows since 2019 at least and VBA can create and modify them but the graphical editor is missing. Power Queries are a relatively recent innovation. Microsoft surely could have made Mac compatibility a design requirement for the editor if they'd wanted to. Why they would kill the graphical editors but leave all VBA's capabilities to create and edit UserForms and PowerQueries intact is a mystery to me.
< My suggested project would be to reverse engineer that [FRX] format
I've taken a quick look at FRX files with the idea of doing exactly that. Created several very simple UserForms with small differences, read then into worksheets with VBA's Binary I/O and compared them hoping studying the differences would reveal their structure. All I can say is it's not real simple or obvious! I might pursue it sometime just for fun but probably not since Richard helped me see that compatibility with the VBE's UserForm editor isn't required for a Mac editor.
To Richard Brenner:
< Or am I misunderstanding your comment?
I know how to open code editing panes manually within the VBE. It's just my screen gets really busy and confusing when the VBE window with several code editor panes and my UserForm editor workbook with several UFs being edited are all open at the same time. If my editor could open just code panes without the VBE window, it might be possible to clean the display up.
Hey, I just noticed VBE's main menu includes an Add-Ins item just like regular Workbook windows. I haven't gotten very far learning about Add-Ins yet but I'm wondering if it's possible to integrate our user-written UF editors into the VBE with an Add-In? Or is that how your editor works? (I had the idea it was an Add-In for target workbooks.)
There’s been a lot of discussion about solving this problem for the Mac. I would like to propose a possible plan to solve it.
As you know when you export a user form two files are created - a .frm file and a .frx file. The .frm file is the vba code in plain text with a little header text at the top of the file.
The .frx file is the definition of the GUI - window definition plus the definition of all the controls. This file is a binary formatted file that is not published by Microsoft.
My suggested project would be to reverse engineer that format then you could create a simple GUI generator on a normal sheet. A vba routine would read the sheet and generate the .frx file and then read it into the project. This would get us most of what we are missing on the Mac and could be implemented as an add-in.
I have seen some discussions about the frx file format but no comprehensive layout of the format.
Ideally Microsoft would implement the GUI editor but they are working with 30 year old code no doubt patched a thousand times to go from 16 bit to 32 to 64 bit plus all the security features of macOS that hinders them from just writing files anywhere they want. It’s not an easy problem for them to solve but I personally don’t know anyone using the web panels they are pushing.
I understand this is a bit of a moon shot but us Mac users might have to take things into our own hands to keep supporting an otherwise great platform.
To Steve Van Dellen (2021/07/25 11:45):
>Looks to me like VBA has all the necessary tools except I don’t see how to open
>the code editor without the VBE UI.
In the Project Explorer, Ctrl-Click a form and click View Code in the menu that appears. Or am I misunderstanding your comment?
To David Swift (to Richard):
< Are you using a task pane as a form?
Panes might be another better way to structure the editor display that my method. I can't figure out how VBA can do anything with Panes except split the view of a worksheet into multiple panes. Could you point me to something to read? Thanks.
To Richard Brenner and David Swift:
< Am I right you are not using an add in to create the forms?
That's correct. My editor is a normal workbook. When you open it, it displays a UserForm with ComboBoxes to select an open Workbook and a UserForm in the selected Workbook. When you select a UF, it exports the UF from the selected WB, erases all the VBA code except the procedure that builds the display if there is one, imports the UF into the editor WB, Shows it and connects instances of classes that handle Mouse events to the UF and each of the Controls. The event handlers let you select UFs and Controls and edit their geometry much like the VBEs UF editor. You can open multiple UFs from multiple WBs at the same time but the display gets really busy and confusing especially if you've also opened the VBE to edit code at the same time.
I've never used Add-Ins. I've starting reading about them. They may help cleanup the display. Thanks for pointing me to them.
To Richard Brenner:
< I’m narrowly focused on one class of UserForm authors
I see I’ve been making the problem too hard. I share UFs with Windows users sometimes which is why I’ve been worrying about compatibility with the VBE’s GUFEF. But my editor runs on Windows so I can just tell them to use it to make changes to UFs they get from me. If volunteers or a company wants to offer an editor publicly, I expect they’ll replace the VBE UI altogether rather than trying to co-exist with it. Looks to me like VBA has all the necessary tools except I don’t see how to open the code editor without the VBE UI. But I can’t see anyone taking that project on without some sort of blessing from Microsoft which isn’t likely or they’d just enable the existing VBE UF editor. Thanks for helping me clarify my thinking.
To David Swift:
>Am I right you are not using an add in to create the forms? Richard might put me right if
>necessary, but his method does use the AddIn method I believe.
To Steve Van Dellen:
>Is your package able to update the User Form object structures in the
> VBComponents or FRX files?
I’ve never tried it, but I can say “no” to FRX with a high degree of confidence. Beyond my capability. But since FRM is a text file, now that you mention it, it might be possible. Good idea.
>My editor can start with a UF created with the Windows VBE and put code in
>the initialization procedure to modify and add to it.
Interesting. Some overlap with my stuff, no doubt. I’m narrowly focused on one class of UserForm authors: those who are not able to (or seriously want not to) undertake a development process that must cross MacOS Office 15+ and any other system to get the job done. They might target multiple systems for the finished product, but they want to do (or have to do) development on a single system, MacOS Office 15+. Many of them have no choice.
>These problems would go away if the editor could edit the VBComponent or
>FRX file but I haven’t found any description of their structure or an API to
>operate on them.
Also interesting. In my comment in this discussion on July 20, I mentioned that I had two primary goals for this work. The second: “to give myself a way to avoid having to manually reverse-engineer a bunch of UserForms for existing projects just to make small updates and extensions.” I think of the machine that could capture those forms as a “UserForm miner.” Sounds like you’re trying to do exactly what I was saying my second goal was. I’ve made very little progress on it tho, because I believe that what I’m making to get me to goal #1 (create UserForms) will teach me a lot of what I would need to learn to do goal #2, if someone else doesn’t make something I could use before I get that far.
>I’m not very familiar with the UserVoice forum. Is the “bumf” (whatever that is?)
>David refers there somewhere?
No idea. Totally clueless. David, thanks for clearing this up.
>I do think a graphical editor is nice to whip out simple forms quickly but I see
>the advantages of being able to share structures and properties across large,
>complex apps with your approach.
The current Graphical UserForm Editing Facility (to myself, GUFEF) is great for private use. But for industrial strength applications, we really need something that’s programmable and which supports libraries of UserForms that can be shared across projects. I think maybe we’re both headed somewhere in that direction.
To Steve Van Dellen
Hi Steve. The bumf I refer to is The MS info regarding creation of Add-Ins. (Emphasis on tedious…
useless or tedious printed material.)
Am I right you are not using an add in to create the forms? Richard might put me right if necessary, but his method does use the AddIn method I believe.
As AddIns are supposed to be cross platform, might it help with the compatibility issue you are speaking of?
To Richard Brenner:
Is your package able to update the User Form object structures in the VBComponents or FRX files?
I’ve been playing with a UserForm editor for Mac too. It just adds a VBA procedure to the UF’s CodeModule to generate the UF dynamically, This works for my odds and ends of personal stuff but I don’t think dynamically generating the display is a good starting point for a possible publicly available editor. I’d like both Mac and Windows users to be able to use the editor and the UFs it creates. My editor can start with a UF created with the Windows VBE and put code in the initialization procedure to modify and add to it. The editor has a hard time dealing with some situations but the worse problem is I can’t figure out how to keep Windows users from editing the UFs with the Windows VBE. You can imagine the confusion when what they see in the VBE doesn’t match what they see when they “Show” the UF and vice versa. These problems would go away if the editor could edit the VBComponent or FRX file but I haven’t found any description of their structure or an API to operate on them.
I just set out to replicate the VBE’s graphical editor without thinking about other approaches. I do think a graphical editor is nice to whip out simple forms quickly but I see the advantages of being able to share structures and properties across large, complex apps with your approach. I’m not very familiar with the UserVoice forum. Is the “bumf” (whatever that is?) David refers there somewhere?
To David Swift:
>Are you using a task pane as a form?
I am not, tho I must admit it never occurred to me. I set out to make a tool that could do two things: (1) Create new UserForms for new projects, and (2) Snarf existing UserForms into the tool to enable ongoing maintenance and enhancement wholly within the tool from that point forward. The purpose of (1) is obvious; the purpose of (2) is to give myself a way to avoid having to manually reverse-engineer a bunch of UserForms for existing projects just to make small updates and extensions. So I think that’s why it never dawned on me to do anything really clever with new facilities. I’ve made good progress on (1), not so much on (2) but I’m convinced that it remains possible.
To Richard Brenner:
>I’m less optimistic about the idea of “levering Microsoft”. What a concept! After that we could work on world peace. :) <sorry>
Oh sorry - overextrapolating on your comment about the possible serious problems of options a & b.
I’ve had a brief look at the add-in bumf and it does seem like it wouldn’t be too hard to get our heads around. Very much looking forward to your future information on the subject.
Are you using a task pane as a form?
To David Swift:
>…either creating a simple interface ourselves, or better still levering Microsoft into
>finishing their job. Either way we all get userforms. So if the only expected return
>was the userform capability, as long as it happens one way or the other wouldn’t
>the crowd be pleased?
I agree that the crowd would be pleased either way. I’m less optimistic about the idea of “levering Microsoft”. What a concept! After that we could work on world peace. :) <sorry>
>By the way, your explanation reminds me that we don’t in the least need a visual
>WYSIWYG interface at least initially.
Exactly. Actually, I find Excel’s Graphical User Form Editing Facility much more difficult to use than my thing. Capabilities like relative positioning of user form controls, single-point definitions of user form and control attributes, and defining and updating packages of user forms turn out to be huge advantages for anyone who maintains multiple forms across suites of projects.
>We would probably all be happy for the present drawing the thing on graph paper
>and plotting the coordinates into an add in list form (if that’s how it works). Better
>than nothing. Is that how your current system works or have you got further than
I wouldn’t say “further”, I’d say maybe a slightly different direction. For example, I do find Excel’s Graphical User Form Editing Facility to be convenient for small numbers of simple forms. But I’d been unhappy with that for a while. My problem is larger numbers of not-so-simple forms, groups of which need to have similar look-and-feel. So when a client changes a logo or a font preference, I didn’t want to have to make that change N times.
Here’s how it works at a very high level leaving out important details:
You specify or edit Excel user forms using a worksheet for the basic
properties of the form, for specifying what controls you want, and their
size and placement, and a few other basic properties. Then you use a user
form for the other properties of the controls in the form. After you’ve
specified your form in this way, you issue a command to generate your form
and insert it into the target Excel workbook (or workbooks).
To Anonymous (July 18, 2021 23:00)
>would you consider sharing your add-in? I’d love to see what you
>built to create userforms.
Reasonable. My answer: Yes and no.
The “no” part: My concern is that uncontrolled release might make it more difficult to find resources to develop it further. And I don’t know enough about code security or redistribution controls to have confidence about releasing operational demos safely.
The “yes” part: What I can do is produce a tutorial with screenshots that will let you see what I’ve done so far. And I’d be grateful for feedback and suggestions. I’ll post a link in a month or so.
And obviously if the right group got together to push this forward, I would contribute what I have.
By the way, just to be super clear, what I have does not replicate the Windows or earlier MacOS capability. It’s a workaround, not a replacement. Essentially, it’s a UserForm generator. To use it, you enter parameters describing your form, all its controls, and all their non-default attributes. Then you push a button, and the generator inserts the form into the target workbook.
There may be a way forward here. Just thinking hypothetically…
With your option B, the crowd could have it simply explained that the objective is to enable the user form in one of two ways; by creating a simple interface ourselves, or better still levering Microsoft into finishing their job. Either way we all get userforms. So if the only expected return was the userform capability, as long as it happens one way or the other wouldn’t the crowd be pleased?
By the way, your explanation reminds me that we don’t in the least need a visual WYSIWYG interface at least initially. We would probably all be happy for the present drawing the thing on graph paper and plotting the coordinates into an add in list form (if that’s how it works). Better than nothing. Is that how your current system works or have you got further than that?