top of page
  • John

Multiple choice UserForm in VBA using a ComboBox

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'.


Multi choice UserForm - ComboBox closed
Multi choice UserForm - ComboBox closed









Multi choice UserForm - ComboBox open
Multi choice UserForm - ComboBox open











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).

modules
.zip
Download ZIP • 3KB





This second .zip holds a macro-enabled Excel spreadsheet (i.e. a .xlsm file) with all of the code.

combobox example
.zip
Download ZIP • 29KB

0 comments

Comments


bottom of page