Worksheet radio button replacement in Excel VBA

Note: this works only for radio buttons on a worksheet and can’t be used in user forms

A recent security update by Microsoft broke one of my clients Excel VBA applications which was using MSForms 2.0 radio buttons. The Excel workbook in question shows several tabular ranges on a single sheet. Each table has radio buttons and list boxes attached for custom sorting and filtering. Unfortunately, only ActiveX controls (MSForms 2.0) support more than one radio button group on a single sheet – so there was no way to recur to inbuilt Excel sheet controls.

The fix as advised by Microsoft didn’t work in our case – and since it is more hassle to discuss uninstalling a MS security update with the security chief, we decided that I should write a vba-only custom radio button group as a workaround.

To use it follow these steps:

  1. Make sure you include the class file in your project (“radioButtons.cls” ). See the download link at the end of this article.
     
  2. Place an empty chart somewhere on a worksheet. Name the chart object and style its background and border as you like.
     
  3. Add two images (or graphics) to the chart area – one for the deactivated and one for the activated radio button state. They should have the same size. In the picture properties set the title (under alternate text) to “deactivated” and “activated” respectively (important!)
     
  4. Add a textbox to the chart area and style it. Set the title to “caption”.
     
  5. In your workbook initialization routine (module or code-behind) add the following code:

    the addButton  class function takes the following parameters:
    button name: i.e. “radioButton_asc”, “radioButton_desc”
    caption text: i.e. “asc”, “desc”
    callback: to be called when clicked, i.e. “SortOrderButtons1_Click”
    button state: “deactivated” or “activated”
    cell selection target: cell to be selected after click event (default: “A1”)
     
  6. The pictures and textbox act as templates for rendering the buttons as given in the initialization routine. You do not have to create all buttons and captions, this is provided for in the initialization. On each worksheet activation the class will check, if the buttons configured are there and if the class is attached to the chart container. If not, the buttons will be created and drawn and the class will attach to the container. This is done only one time. Subsequent worksheet activations will only attach the class to its container.
     
  7. When clicking on a radio button the clicked button gets activated and the function hook is called.
     

You may use as many buttonGroups on a worksheet as you like. Just repeat the steps above.

This simple solution has several advantages:

  • easy to handle
  • avoids dependency on MSForms
  • more than one button group on a single worksheet (which is possible with the active-x controls in MsForms 2.0 but not with the inbuilt Excel controls)

 

Links:

 [Download radioButtons.cls (zip)]

John Peltier: Chart Events in Microsoft Excel