Wednesday, October 15, 2014

Adding controls to Userform Toolbox

The Problem

As I was thinking about how to design  a new tool to make VLOOKUP building simpler, I realized there was no control from the Userform Toolbox to do what I see in Microsoft's UI, i.e. to select a range.  My concern was not just doing all the programming to create a new control or to add code to one that would allow me to mimic what I saw in the Microsoft UI.  Heck, I had never done such a thing and it seemed like a huge learning curve.


Figure 1 - Standard Userform Toolbox

The Research

Of course I went the easy route: Internet search on "excel vba userform cell select".  Within a few clicks I found what I needed.  (NOTE:  As I was writing this I found one of the books on my desk has this information, but I would not have known what "refedit" meant until I found it in one of the links.)

I found three things, at least 2 are useful.  Life is good!
  • There is a control that does what I want.
  • How to add it to my toolbox.
  • There is a slew of other controls available.

The Steps


  1. Right-click in the toolbox and select "Additional Controls..."
  2. Scroll down to RefEdit.Ctrl
  3. Check the box next to RefEdit.Ctrl
  4. Click the OK button

Figure 2 - Additional Controls Dialogue
Figure 3 - RefEdit.Ctl selected


Figure 4 - Toolbox with RefEdit Control

Two more things!

Controls in the toolbox can be removed and customized.  Access to these is similar to adding a control: Right-click on the tool for the context menu.

Removing a control

Remove the control by selecting Delete Image from the context menu. This removes it from the active toolbox but it is still available to be re-added.
NOTE:  No warning is given after clicking the "Delete Image" option.

Customize a control

Actually the option is to Customize Image.  You cannot actually make the control work differently.  After clicking on Customize Image the dialogue below allows changing the Tool Tip Text and the picture on the toolbar.
Figure 5 - Tool Customization Dialogue