The Microsoft Forms 2.0 Object Library (FM20.dll) really lacks a number of controls. One which I seem to miss the most often is a “grid” control which will let users select rows and edit data in multiple columns. For this sort of functionality I normally use a
ListView control which comes as part of the Windows Common Controls (MSCOMCTL.OCX). It takes some VBA plumbing to get it to work but does the job in a clunky sort of way.
The problem with the Windows Common Controls OLE Control Extension is that it’s only available as a 32-bit binary. You quite simply cannot use it in 64-bit Excel – and 64-bit Excel is becoming an ever-increasing consideration for my clients. If you search around sites such as TechNet you’ll find that Microsoft helpfully suggests that, for 64-bit Office, you either obtain 64-bit compatible versions of ActiveX controls or create your own. Since Microsoft does not provide a 64-bit version of its
ListView, I decided I would make my own grid control – not from scratch, but rather using
Labels in a
Frame to create a grid effect. There’s nothing super clever here: code to manage groups of dynamic controls, wrapped up in an add-in with some simple methods and properties to make it easy to use.
Once I’d finished the first version and started to use it, I decided it would be really nice if I could put “data validation” in the grid – which would probably mean putting in
ComboBoxes instead of
TextBoxes for grid items which need a dropdown. Having a mixture of both would be a slight pain (but not impossible) to implement because my
Collections were strongly typed, so I had a hunt around the Object Browser and I noticed that the
MSForms.TextBox class exposes both a
DropButtonClick() event and a hidden
Furthermore, to show or hide the dropdown button it has a hidden
If I could give some of the
TextBoxes in my grid the appearance of a
ComboBox then that would be pretty handy because it would be a very simple change to my add-in.
ShowDropButtonWhen properties can’t be configured in the Properties Window so you have to set them at runtime. Not a problem for me because my
TextBoxes are all created at runtime anyway. When the
DropButton is clicked and the
DropButtonClick() event is raised you don’t get a nice dropdown pane supplied for you, so you have to create your own. That isn’t hard to do. For a simple example, create a
UserForm and add a
TextBox1 to it. Then add the following code to the userform’s module and run it.
Option Explicit Private WithEvents mobjListBox As MSForms.ListBox Private Sub UserForm_Initialize() TextBox1.ShowDropButtonWhen = fmShowDropButtonWhenAlways End Sub Private Sub mobjListBox_Change() TextBox1.Text = mobjListBox.List(mobjListBox.ListIndex) mobjListBox.Visible = False End Sub Private Sub TextBox1_DropButtonClick() If mobjListBox Is Nothing Then Set mobjListBox = Controls.Add("Forms.ListBox.1") With mobjListBox .Top = TextBox1.Top + TextBox1.Height .Left = TextBox1.Left .Width = TextBox1.Width .SpecialEffect = fmSpecialEffectFlat .BorderStyle = fmBorderStyleSingle With .Font .Size = TextBox1.Font.Size .Name = TextBox1.Font.Name End With .AddItem "a" .AddItem "b" .AddItem "c" .AddItem "d" .AddItem "e" .AddItem "f" .AddItem "g" End With Else mobjListBox.Visible = Not mobjListBox.Visible End If End Sub
Of course one could take the implementation further, but hopefully that’s enough to give you the idea. As it turns out, I didn’t take it any further because I then realised it would be even easier to use
ComboBoxes for all my grid items and give them the appearance of
TextBoxes when “data validation” isn’t needed. All that needs is one line of code:
ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever
ComboBox doesn’t have as many text formatting properties as the
TextBox, but I’ll worry about that if I ever need them. For now, I’d much rather keep things simple.