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 TextBoxes
and 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 DropButtonStyle
property:
Furthermore, to show or hide the dropdown button it has a hidden ShowDropButtonWhen
property:
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.
The DropButtonStyle
and 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 TextBox
called 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
Sure, the 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.
Interesting. Have you considered open sourcing the control?
LikeLike
Hi Chris – no, I wasn’t planning on it at this stage.
By the way, I like your blog!
LikeLike