Excel VBA Multipage Page Tab Focus Anomaly

42 Views Asked by At

I have a Non-Modal UserForm (named UF1), with a MultiPage (MP1) that has a Command Button (CB1) on Page 1 (Pg1),
and an Empty Page 2 (Pg2)

I then do the following ("iwc:" denotes an Immediate Window Command);

iwc: UF1.Show

Select CB1                                 'it gets the Focus
iwc: ? UF1.MP1.Page1.ActiveContol.Name     'CB1

Press <Tab>                                'the Pg1 Tab gets the Focus
iwc: ? UF1.MP1.Page1.ActiveContol.Name     'still CB1

Press <RightArrow>                         'the Pg2 Tab gets the Focus; if Page1.CB1 still  
                                           'had the Focus the RightArrow would have acted   
                                           'on it (and done nothing)   

iwc: ? UF1.MP2.Pg2.ActiveContol.Name       'Runtime Error `91: Object Variable Not Set  
                                           'Selecting a Multipage Page moves the Focus to  
                                           'the Control with the Lowest TagIndex;  
                                           'in this case Pg2 has No Controls
  

The following Function (ufActConCSV) returns the "path" of a UserForm's ActiveControl as a CSV.
For the example above it returns "UF1,MP1,Pg1" when the Pg1 Tab has the Focus.
It works around the anomaly using SendKeys()

    Function _
ufActConCSV(iUF As UserForm) As String

    'Returns iUF's ActiveControl's "Path" as a CSV
    '   eg "MultiPage1,Page1,Frame1,CommandButton1"
    '   Returns Null if iUF is Not Visible, or is a Blank UserForm

    Dim zChi As Object
    Dim zMP As Boolean
    Dim zMPPg As Object

    Set zChi = iUF.ActiveControl
    If Not zChi Is Nothing Then
NChiL:
        ufActConCSV = ufActConCSV & zChi.Name & ","
        zMP = TypeOf zChi Is MSForms.MultiPage
        If zMP Or TypeOf zChi Is MSForms.TabStrip Then
            ufActConCSV = ufActConCSV & zChi.SelectedItem.Name & ","
            Set zChi = zChi.SelectedItem
            If zMP Then Set zMPPg = zChi
        End If
        If zMP Or TypeOf zChi Is MSForms.Frame Then
            If Not zChi.ActiveControl Is Nothing Then
                Set zChi = zChi.ActiveControl
                GoTo NChiL
            End If
        End If
    End If
    If Not ufActConCSV = "" Then _
        ufActConCSV = Left(ufActConCSV, Len(ufActConCSV) - 1)
    If Not zMPPg Is Nothing Then
        Debug.Print UF1.MP2.Pages(0).ActiveControl.Name
        SendKeys "{RIGHT}", True
        DoEvents
        If zMPPg.Parent.SelectedItem.Name <> zMPPg.Name Then
            zMPPg.Parent.Value = zMPPg.Index
            ufActConCSV = Left(ufActConCSV, InStr(ufActConCSV, zMPPg.Name) _
                + Len(zMPPg.Name) - 1)
        End If
        On Error Resume Next
        zChi.SetFocus
    End If
    
    Debug.Print "ufActConCSV{}=" & ufActConCSV
    End Function

Very messy; is there a simpler way to find out if a MultiPage's Tab has the Focus?

0

There are 0 best solutions below