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 the suggestion and for the support of this feature. If you meant to vote for the Data Form, which is different than VBA UserForms, please change your vote to that suggestion instead. From reading the comments on this one, it seems that some clarification was needed.
I updated the title and description for this one and we’ll continue to monitor for comments and votes.
Here’s the link to the Data Form suggestion – https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/17930662-restore-data-form-functionality
Steve K [MS Excel]
Ive been playing with the form controls and think I may have thought of a way round. Its a bit circuitous though. There probably is a way to directly access the form controls in the code but as you say, its certainly very obscure.
The form controls have a right click "format control" option that reads and writes to cells or ranges of cells within a worksheet. The output is in the form of an integer in for example list boxes, and you could write code to feed or write from the cells that the form control is linked to.
The button control seems to be a simple way of running a macro you have recorded or developed. Option buttons write an integer to a chosen cell and can be grouped in the group box and work independently from those outside of the box.
If you had text or numerical values to input or output, this would be done in a cell directly through code.
Also by using spreadsheet functions like Vlookup, SumIf etc. within the cells referenced by the form controls, a lot of coding could be obviated.
Heiner, Ive found this with a few possible clues:
For example it says this:
---"Properties - I don't know of a button or keyboard shortcut, but you can drag the Properties window out of the VB Editor so it floats above the worksheet. The properties displayed are what is currently the active object in the VB Editor, not on the worksheet."
Let me know if you find anything else...
EDIT - Just tried it and the properties box doesn't seem to work with the sheet form controls in the way described...
@Will, you talk rubbish, why do we need windows. MS promised in there advertisements progress and the same as windows. But nothing is far from the truth. De Excel development department for the Mac at Microsoft is pure ****, at least the managers. We pay the same for both versions. If someone hurts you'r feeling, start crying. How many years are we asking for to be equal as the windows version. From day one. 1145 votes as of today. No they are busy with an iPad version. Yep, really important compared to a Mac version. iPad version is nice to show off, but the real work has to be done on the Mac.
Barbara: yes exactly! Thank you.
Heiner: I’m going to have a look... perhaps there is some spreadsheet formatting for the control object within the sheet rather than the code. A bit like locking a picture to float over cells or within one etc. Haven’t really used that much either though. Sounds interesting!
(At last a useful conversation!)
I have noticed over the years, as I was holding my breath, that at various times a new versions of Excel would have some improvements without any announcement. So I thought just now, let's see what if perhaps something has changed. Well there still is no Form editor or designer. But oh look: MacOS Excel 365 version 16.39 has visual controls on the developer tab ribbon (I guess they have been there but to me they always seemed do nothing). So I just now I guessed that I could perhaps mimic a userform with a sheet with those control dropped into place. I tried it and it is barely usable. The is no Frame control to group them. Groupbox (for grouping checkboxes or option buttons) appears unusable. Or perhaps whatever is intuitive to the programmers is not intuitive to me. Is anyone aware of a tutorial for how to use those control?
Tall and short adults pay the same price because their clothes does the same effect. When MS sells Excel for MAC users, they must sell a program with the same features (so it will work perfectly for everyone).
I think we should come here often to show developers that we're alive and still waiting for this. Please, we need UserForm!
Will, Barbara, Heiner, Mauricio,
This discussion is just beginning to become interesting and constructive.
Will, don’t go! Crigou (check out the obscure Migou as one spelling of Mi-go; a term for the Yeti, the legendary and rarely seen “wild man”) has his angle and it certainly always seems to be off topic. I have been ignoring his interjections for years - but to be fair things may have been different without your kind apprisement.
A couple of things then;
1. I’ve been using excel vba for Mac for years now to create a few cross platform workbooks with some quite complex useabilities. The user form was probably one of the first elements I made use of. Although activex was never available for Mac, for my uses including some very functional userforms, I always found a cross platform workaround on the net.
The last non subscription version of excel that did have the user form was either 2011 or 16. Not sure which! Anyway 365 doesn’t have it as we know and it’s native file versions I believe are quite different being xml based.
So on Microsoft’s side, the commensurately required Mac based ground up rebuild of an application like excel would take a huge amount of work and time. And Mac programmers aren’t generally really keen on working for Microsoft!
Having said that, it’s not really fair to hold clients to ransom because of their profession. My design based work for example requires the use of a Mac. But I also believe excel and vba is required to maintain a professional edge in my particular branch of the industry too. An analogy might be that tall adults don’t pay more for their clothes than short adults just because they use more cloth and take a bit more work to make. So why should Mac users?
Frankly, the Office suite has become so intrinsic to the running of society in general, it should be made mandatory to keep it developed in a cross the board and up to date way. It’s possibly as important as road repairs. Maybe it should be nationalised! (Not really serious, but hopefully it shows it’s no laughing matter. Livelihoods and industries have built themselves on these technologies.)
In conclusion, I have a gut feeling based on the recent deceleration in product improvement that we may have a long wait.
So in case it got missed before, my most emphatic vote Is for the userform to enabled natively on the Mac.
Sean Magula commented
I agree with you 100% Will, Crigou takes away from the nature of this thread/forum, "How can we improve Excel for Mac". Why don't you change your whole environment to Windows and run Excel...has nothing to do with how to improve Excel for Mac. It detracts from the intended purpose.
I have read many of the comments, and everyone is right on some points, what needs to be made clear, is that Microsoft has not put the userfoms on Mac (for forgetfulness, lack of resources or lack of interest, etc...) Of course we can use other resources as parallels to run Windows virtually, but the fair thing is that if we pay for a service, we revived the full service (all excel).
Heiner, Point taken. It’s just frustrating that every time someone makes a post here Crigou jumps in and tries to explain the inner workings of Microsoft’s software development and their business model, when he clearly has no real information to back up his claim. He sends them to third party sites that are generally conjecture as if we’re all ignorant. It discourages folks from participating in the conversation. My goal is to make it as painful for him to post here as he’s attempting to make it for others. If it means that I have to call a person who is actively trolling a troll, so be it. What I don’t want is for folks to come here and think that he’s speaking on behalf of the moderators. His comments are irrelevant to the conversation.
I take it that “yourself” is directed at me, but I don’t identify with my username. My online persona won’t be upset if you think he’s a bully for not lying down while this guy calls us all ignorant.
Nor do I assume that the person signing “Crigou” identifies with their user name... That said, the persona “Crigou” needs to be discredited. “Crigou” comes here with the sole intention of making others feel ignorant. Crigou should take his comments and post them where they are relevant. The title of the blog might be... “How to design vba forms on a Mac” vice “How can we improve Excel for Mac”.
I would prefer if every time Crigou showed up, someone other than me reminded him that he’s trolling. Or if a handful of you request that I stop, I’m amenable. And we can allow this character to continue to bash folks who come here asking for a feature.
Responding to Will: that is what I have now. I run a Windows Virtual machine on my Mac, with a paid copy of Office and Windows solely so I can teach my graduate modeling course where I teach students how to use the awesome tool of VBA within the Excel software (which they all use for data manipulation).
I would suggest that we not take any comments by anyone personally, nor resort to name-calling, nor making any statements about other persons. In the long run such comments just reflect badly on yourself as they contribute nothing to the discussion.
Apparently he is a seller from MS. Doesn't make any sense he keeps coming here to discourage us to ask for something to come back.
Heiner, Apparently you have not been listening to the troll (Crigou). The answer isn't, "Tell Microsoft what we want." The answer is, "Buy a copy of Windows ($200) and then buy another copy of Excel for Windows ($140) and run Windows on your Mac."... BTW, I think you've hit the nail squarely on the head.
I may be wrong but I don't think that anything that was working in older versions of VBA in Office was removed from the macOS version. There was a time when VBA on Excel under macOS was pretty much either not there at all or so crude that it was strange to have such a rough edge on something so powerful (Office). Over the years MS has tried to make VBA in the macOS version as useful as the Windows version. It is really a recent situation that you can take VBA code from a Windows version and run it under macOS office with minimal adjustments. My sense is that there has been a dedication at MS to keep improving the mac versions of Office slowly. At this point the biggest missing piece is the forms editor. The other piece I am aware of is that various functions that touch the file system need to be modified so that they work the same transparently. For example, the DIR() function to get a list of files in a folder (under macOS it requires considerable hacking to get a list of files). I am sure there are many other functions that are not cross-platform. So I think the key is to get them to understand what is important to us (the clients).
You've asked a good question below:
" ... I just find strange that they had the user form in the 2011 excel for Mac so it makes one wonder why they did not include it in later versions. ... "
My answer, according to my own feeling, is that, in the software engineering field, deleting a function assumes less technico-financial effort than continuing to maintaining / correcting / recoding it. Furthermore, in this case, the concerned Mac user has the option to run the most powerful Excel version in Windows, on the same machine.
To follow your last sentence below: Office product managers are considering office-on-mac-users just as important as the income calculated in multiplying number-of-office-licenses-for-mac by price-per-sold-license. Not more important.
What bothers me the most is that MS hasn’t reacted to this question in all the years that this thread is going. Please MS, don’t play dead any longer...!!!
Sean Magula commented
Your logic is flawed. How can it be an illusion when it used to previously exist.
You can link all the articles in the world, but it still won't deter us from requesting a product/feature addition on a site that is asking for suggestions/feedback. Especially if the feature existed before.
Feedback is how products get better and improve. Ignoring customer feedback and suggestions will turn off customers to the product. It is obvious that this is not a priority for MS but hopefully it is on their radar.
Lee Townsend commented
I am not convinced MS is paying attention. I have seen nothing from them about their adding userforms to the Excel Mac 365. They are too busy developing a scripting language for cross-platform compatibility. Why not just use Python? It's a scripting language. I guess they would rather waste their time and money developing a new language instead of using one that alot of people already know hence would be more inclined to buy Office. Of course they have to make it Mac, PC, web, iOS, and Android compatible. As far as I know, Python already works on all the platforms. I would love to see their Mac income when they went from 2008 (no VBA) to 2011. You would think they would have learned that Mac users are important.
Thank you. If someone comes here and say sh**, I answer. Specially if they try to reduce this topic seriousness. That's why I answered him.
You're right, we shouldn't have to pay the same price in these conditions. About a lawsuit, in Brazil we die waiting for justice.
Something doesn't looks right, though. Patrick just said that Excel 2011 had user form. So, Microsoft made it on purpose?