Get The Active Control In A UserForm

Recently, I had the need to find the active control on a UserForm for a dialog box I was creating for Word 2003. The problem was that most of my controls were on a MultiPage control. Whenever the active control was on the MultiPage, UserForm.ActiveControl returned the MultiPage, not the control I wanted.

My solution was to write the following function, GetActiveControl which would essentially ignore MultiPages and Frames to return the “real” active control:

Public Function GetActiveControl(ByRef Source As UserForm) As Control
Dim Ctl As Control
  Set Ctl = GetSubActiveControl(Source.ActiveControl)
  Set GetActiveControl = Ctl
End Function

Private Function GetSubActiveControl(Ctl As Control) As Control
Dim CtlType As String
  CtlType = TypeName(Ctl)
  Select Case CtlType
  Case "MultiPage"
    Set Ctl = Ctl.SelectedItem.ActiveControl
    Set Ctl = GetSubActiveControl(Ctl)
  Case "Frame"
    Set Ctl = Ctl.ActiveControl
    Set Ctl = GetSubActiveControl(Ctl)
  End Select
  Set GetSubActiveControl = Ctl
End Function


As you can see, I use a private recursive function to do the real work of finding the control. I suppose I could have done it some other way, but this seemed to be the most straightforward.

Excel Name Manager

One of the links in the Heroforge Mailing List referred to this Excel Name Manager. It is an add-in for Excel that makes is much easier to sort through and manage named ranges in Excel. I have barely tried it out, but it appears to be really good.

There are also a lot of other Excel utilities on their download page, but I haven’t checked them out yet.