I have a whole self-created library of code snippets that I use when creating VBA applications for clients. One of those snippets (actually, a bit more than a snippet) is a UserForm that holds 4 Controls - a ComboBox, a Label and two CommandButtons (OK and Cancel). This UserForm allows me to get a choice from the user when you need more than just Yes/No or OK/Cancel options. I'd say I use this in more than half of the applications I create. The beauty of it is that it is just 'plug n play'.
You can download the code for the UserForm (and example usages in a standard Module), at the end of this post, but if you want to set this up yourself then ... add a UserForm with a Label, ComboBox and two CommandButtons. Set the following properties:
For the UserForm:
Name = UserFormComboBox
Height = 96
Width = 169
For the Label:
Name = LabelText
Height = 12
Left = 4
Top = 4
Width = 151
For the ComboBox:
Name = ComboBoxSelectResults
Height = 18
Left = 4
MatchEntry = fmMatchEntryComplete
MatchRequired = True
SelectionMargin = False
Style = fmStyleDropDownList
TabIndex = 0
Top = 20
Width = 151
For the Cancel CommandButton:
Name = CommandButtonCancel
Accelerator = C
Cancel = True
Caption = Cancel
Height = 20
Left = 22
TabIndex = 1
Top = 42
Width = 64
For the OK CommandButton:
Name = CommandButtonOK
Accelerator = O
Caption = OK
Default = True
Height = 20
Left = 90
TabIndex = 2
Top = 42
Width = 64
Most of these properties are fairly self-explanatory but a few not so much. For info on these (and all Control / UserForm properties) see the VBA Language Reference, section for Microsoft Forms Properties.
The following is the code of the UserForm. If setting this up yourself then copy this and paste it into the code-behind for the UserForm.
The UserForm includes the code that was explained in my post Changing UserForm Button colour on mouse hover so that the OK and Cancel buttons respond to mouse movements. See that post for explanation of that code if required.
And this is a usage example using an array of Strings and displaying the text of the option selected by the user. Put this in a standard Module and run it:
This is another usage example using a Variant holding an array of Strings, making the UserForm wider than the default and displaying the index (0-based) of the option selected by the user. You can put this in the same (or any other) standard Module and run it:
Simple as that. Feel free to use this in your next VBA application!
And a quick plug: do you find it annoying how, having run the 'example usage' code, when you go back to the VBE the editing position has moved from where you were (i.e. somewhere in the Example1() or Example2() procedure) to the UserForm designer? I do. That's one of the reasons I created VBE_Extras. Both the 'History' feature (one key-press to navigate back to where you were) and the 'Sub to run' feature (set a 'Sub to run' then run it from anywhere using F6) can help with this.
Here are the attachments if you would rather download everything. This first .zip holds the .frm and .frx files for the UserForm and also a .bas file for the two usage examples. Download and unzip, then use the File > Import File option in the VBE (for the UserForm, ensure the .frm and .frx files are in the same folder with the same name other than the extension, then you only have to import the .frm file ... the .frx will be imported automatically at the same time).
This second .zip holds a macro-enabled Excel spreadsheet (i.e. a .xlsm file) with all of the code.
Comments