CODEWITHSUNDEEP
vbaexcel
Dominic Marchese
Dominic Marchese

Reputation: 11

Get Activecontrol inside multipage

I am trying to get the name of theListbox that I just selected ("ListBox1").
Caveat: ListBox1 is located within Multipage1 (on the first tab).

Private Sub ListBox1_Click()
Dim m As String

m = Me.ActiveControl.Name
MsgBox (m)
End Sub

Since I have selected the ListBox1, I'm expecting the message box value to be ListBox1.
Instead I am receiving Multipage1.

What should I be doing differently?

Upvotes: 1

Views: 1490

Answers (2)

Malcolm Farrelle
Malcolm Farrelle

Reputation: 187

An old post but I landed here with the sames issue. Unfortunately this answer didn't work for my scenario (controls positioned within nested frames on a multipage). But user6432984 gave a great headstart.

The key to the proper solution is that the active control (reported by "ActiveControl") is the top level control that is active on the userform. It may have either:

1) Another nested "ActiveControl",
2) A "SelectedItem"
3) Neither of the above - in which case it really is the "ActiveControl" you want.

So, the technique to drill-down requires that you determine which of 1), 2) or 3) above you have. If you don't have 3) keep throwing whichever of 1) or 2) you do have into the recursive function until you find 3): 

Function ActiveControlName(Object As Object) As String
'modified my Malcolm Farrelle
Dim Obj As Object
Set Obj = Nothing
On Error Resume Next
Set Obj = Object.ActiveControl
On Error GoTo 0
If Obj Is Nothing Then
    On Error Resume Next
    Set Obj = Object.SelectedItem
    On Error GoTo 0
End If
If Obj Is Nothing Then
    ActiveControlName = Object.Name
Else
    ActiveControlName = ActiveControlName(Obj)
End If
End Function

Upvotes: 0

user6432984
user6432984

Reputation:

It helps to consider MultiPage, Pages, and Frames as subforms. If a subform control is active then the parent form will return the subform as the ActiveControl.

Here is the proper way to drill down to the actual ActiveControl.

Function ActiveControlName(Object As Object) As String
    Dim Obj As Object
    On Error Resume Next
    Set Obj = Object.ActiveControl
    If Err.Number = 0 Then
        ActiveControlName = ActiveControlName(Object.ActiveControl)
    Else
        Set Obj = Object.SelectedItem
        If Err.Number = 0 Then
            ActiveControlName = ActiveControlName(Object.SelectedItem)
        Else
            ActiveControlName = Object.Name
        End If
    End If
    On Error GoTo 0
End Function

Demo

enter image description here

Upvotes: 1

Related Questions