Deceptive Appearances: MSForms Textboxes And Comboboxes

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.

Advertisements