mardi 10 septembre 2019

Adding checkboxes in alphabetical order

I have written some code where I loop through Pivot-Items and if a value matches a value within a list of values, a checkbox is created into a userform. I have now added another value to the list, and as expected the userform shows the new value with a checkbox, however it is as the end of the list, although it starts with an "A" and the list is actually alphabetically sorted. I have refreshed several times, opened the sheet etc. but the new value checkbox just won't go to the top of the list of checkboxed where it is supposed to be. The "if" checks are not in alphabetical order and I believe that should not make any difference.

I suspect the reason is that I added the new name last in the pivot table? how do I make the pivot table "forget" that?


    'List Checkboxes
    intTop = 10 'set the cursor for the first checkbox

    Set frNew = Me.Controls.Add("Forms.frame.1")
    With frNew
        .Name = "SingleC"
        .Caption = "Single Clients"
        .Width = 150
        .Left = 180
        .Height = 170
        .Top = 100
    End With

    Set pf = pt.PivotFields("Various Names")

    For Each pi In pf.PivotItems
     If pi.Value = "dcc" Or _
        pi.Value = "eff" Or _
        pi.Value = "mgg" Or _
        pi.Value = "lpp" Or _
        pi.Value = "bgg" Or _
        pi.Value = "gtt" Then
        Set cbNew = frNew.Controls.Add("Forms.Checkbox.1")
        With cbNew
            .Name = pi.Value
            .Caption = pi.Value
            .Left = 10
            .Top = intTop
            .AutoSize = True
        End With
    intTop = intTop + 15
    Else
    End If
 Next pi

I expect the names to be in alphabetical order, i.e. starting at dcc and ending at mgg from top to bottom. Now my list is the one previously (before I added the new value) and the new value is in the end, although it should be in the second place.




Aucun commentaire:

Enregistrer un commentaire