Select Second Pivot Table Row Data After First Row Input VBA Userforms & ComboBox

Question

I have a Userform in which I submit some data, from the data my box populates with more data, and after I select the data from that box I need to select data from for that item from a third box. The data gathered is from a PivotTable.

Box1 is just a supplied combobox while 2 and 3 are directly from a PivotTable. I have the functionality for Box2 working based off Box1 but that's because they are separated by sheets.

The goal is that Box1 can = A,B,C then if Box1=A then Box2 can = 1,2,3 then if Box2 = 1 then Box3= x,y,z.

The problem being in Box3 it returns the information for Box2= 1,2 AND 3 rather than just 1.

My current code is:

    lCommentCount = Sheets(pt).PivotTables("Pivottable1").TableRange2.Rows.Count

    For i = 1 To lCommentCount
        If Me.ptDatabox.Value = Sheets(pt).PivotTables("PivotTable1").PivotFields("ptData").LabelRange.Offset(i, 0).List Then
            OEEDataEntry.commentbox.AddItem Sheets(pt).PivotTables("PivotTable1").PivotFields("ptData").LabelRange.Offset(i, 1)
        End If
    Next i

    For i = Me.commentbox.ListCount - 1 To 0 Step -1
        If Me.commentbox.List(i) = "" Or Me.commentbox.List(i) = "Grand Total" Or Me.commentbox.List(i) = ("(blank)") Then
            Me.commentbox.RemoveItem (i)
        End If
    Next i

Tim Swingle · Accepted Answer · 2015-07-16 14:20:16Z

I have been toying around with this all day and found that you have to make the second row visible. This code works well. Note showing the detail BEFORE counting.

    Sheets(pt).PivotTables("PivotTable1").PivotFields("ptData").ShowDetail = _
True

For i = 1 To lCommentCount
    pti = Sheets(pt).PivotTables("PivotTable1").PivotFields("ptData").LabelRange.Offset(i, 0).Value
    If Me.ptDatabox.Text = pti Then
        OEEDataEntry.commentbox.AddItem Sheets(pt).PivotTables("PivotTable1").PivotFields("ptData").LabelRange.Offset(i, 1)
    End If
Next i

For i = Me.commentbox.ListCount - 1 To 0 Step -1
    If Me.commentbox.List(i) = "" Or Me.commentbox.List(i) = "Grand Total" Or Me.commentbox.List(i) = ("(blank)") Then
        Me.commentbox.RemoveItem (i)
    End If
Next i

If for whatever reason you want to change the information given in the first box then you would need to make it so that on reexit of that first box that you collapse all the bullets so Sheets(pt).PivotTables("PivotTable1").PivotFields("ptData").ShowDetail = False but that needs to be before you start counting items in the list.

Collectives™ on Stack Overflow

Select Second Pivot Table Row Data After First Row Input VBA Userforms & ComboBox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related