MacroProblem-Creating a Top 20 Customer Userform

[cgeorge4]

Hello,

I have a userform almost completed that chooses the "Top 20" Customers from a Pivot Table.

I've assigned a macro to the folowing with no problem:

"Refresh Pivot Table"
"See Top 20 Customers"
"See Bottom 20 Customers"
"Reset Pivot Table"

My difficulty begins right at after I've place the Scrollbar where I want it.

It is horizontal and I've placed it to the right of the buttons listed above.

HERE IS MY CODE FOR THE "TOP 20 CUSTOMERS":

Sub TopNthCusts()
'
' TopNthCusts Macro
'
ActiveSheet.PivotTables("PivotTable1").PivotFields("Portfolio").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Portfolio").PivotFilters. _
Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("Sum of Revenue"), Value1:=20
ActiveSheet.PivotTables("PivotTable1").PivotFields("Portfolio").AutoSort _
xlDescending, "Sum of Revenue", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1
Columns("B:B").Select
Selection.Style = "Comma"
Columns("B:B").EntireColumn.AutoFit
End Sub


I know that I need to change the "20" in my code to the following:

PivotFields("Sum of Revenue"), Value1:=ActiveWorksheet.Range("N2").Value
End Sub

(....I used column "N" because that is where I place the scrollbar)

QUESTIONS:

1) How do I add a "something(?)" that will change numbers when I click up or down on the Scrollbar to see the "Top 6 Customers" for example? I need to be able to see the number change as I click up or down.

2) I need a little comment to the left of the number that will change when someone clicks up or down on the Scrollbar that says "See Top:"

2) How do I create a box that says "Welcome, Please Select an Option" at the top of my Userform?

I feel like these are silly questions but I can't figure it out.

Thanks so much,
Juicy,

 

[cgeorge4]

No need for any response....I worked it out on my own!

Just in case someone needs to know how to add a macro to a scrollbar and how to setup the scrollbar after it has been placed on a worksheet....I'll write down my steps.

1) Record a macro to filter your pivot table by "Top 10". Here is what your code will look like after you've done that:

(...make sure you click on your pivot table as your first recorded step...)

Range("A5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Portfolio").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("Portfolio"). _
PivotFilters.Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sum of Revenue"), Value1:=10

2) Go back and opposite click on the scrollbar and choose "Format Control". Put a 1 in "Minimum Value". Put a 200 in "Maximum Value". Then choose a cell for "Cell Link". This will be where your variable number will be visible. Click OK.

3) Go back to your "TopNthCusts" macro and change the 10 to the following (don't forget the cell link YOU chose....i picked N4 for my code):

ActiveSheet.Range("N4").Value



NOTE: Make sure your "TopNthCust" code has a line to remove all filters. In case it doesn't, add the following as your second line of code:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Portfolio").ClearAllFilters


I hope this helps someone

Juicy,