How to updated pivot tables on different tabs from user form parameters?

[dovermac]

Here is the scenario. I'm a novice at best. This is a macro-enabled workbook for the Board of Education. The workbook is 31MB so not sure where I could post it. If you could see it, you would click on View on the top and then look to the right on the ribbon. Click on the Parameter Selection icon. I'll try to attach this shortly. My work is locked down pretty good.


This allows the user to select the entity to examine. If they do not select an ISD in combobox1, it should default to everything. If they select an ISD like Oakland Schools above, but nothing in Combobox2 than all School Districts will be included and so on. Once they have made their selection, they click OK. Now, what hopefully should happen is the data for their selection should be pulled from the “Combined” tab and put in the Pivot Tables on the Working Pivot Tables tab. Once that is done, another function should take the data from these Pivot tables and “refresh” the graphs and charts on the Summary tab at the front of the workbook.



One thing I’m still trying to correct is the data that populates this workbook comes from a web site that must be downloaded manually (don’t ask). It comes down as csv. You can see from the image above the “code” columns should be 5 characters with leading zeroes. Unfortunately, the source data is not that way. I have saved the source file as xlsm, formatted these code columns as custom since text won’t work and imported them into the workbook. The data moved over to the Pivot Tables (manually done to this point) does not maintain the leading zeroes.

[dovermac]

BUMP

Can anyone help me please? The workbook is now on dropbox if interested.

[Bob Phillips]

Dropbox is pretty big, any clues as to where on Dropbox?

[dovermac]

I'm sorry, here is the link to the workbook. You might have to add the custom tab (CallUserForm).

https://www.dropbox.com/s/dmrhr24naa...Dashboard.xlsm

Thank you for looking!

[Bob Phillips]

Okay, I have the workbook, what next? I can't see a Parameter Selection icon on the VIew tab, I cannot see a combobox, nor any image above the "code".

[dovermac]

Ok, on the View ribbon, right click where there is nothing and left click on customize the ribbon. Change the drop down from popular commands to macros and you should see CallUserForm and CallUserForm2. You only need the first one. Let me know if you get that far.

[Bob Phillips]

Okay, I thought it would already be on the ribbon, didn't realise I needed to do that.

The OK button does nothing, so are you asking for some code that will reset the pivot's source data to the data based upon the selections? Can a user request multiple ISDs/Districts/Buildings?

You really should store this data in a database and just use Excel to pull it out and display it.

[dovermac]

Believe me, I agree with you, but this is how they want it done. I know the ok button does nothing, because I don't have a clue as to how to approach this.

If the user does not select anything in combobox1, than by default everything in the combined tab will be used (or summed up). If a user selects an ISD, but no specific district, than all districts will be used, and so on. Does that make sense?

[Bob Phillips]

Yeah, I got that, but could they select Oakland Schools and Ottawa ISD (as an example), or will it always be a singleton selection or blank?

[dovermac]

My understanding is one school or all, if you know what I mean. Not picking scattered names here or there to compile.

[Bob Phillips]

Okay, here we go. I'll post it in sections, the workbook is far too big.

First, add a new worksheet called scratch, I use this in the code. Uo can hide if you wish.

Second, in PivotTable1 and PivotTable2, deselect the Blanks and School Year from the school years list.

Replace the CallUserForm procedure with the following

Public Sub CallUserForm()
Dim mpForm As ParameterSelection
Dim wsCombined As Worksheet
Dim mpTotalsRow As Long
Dim mpTotal As Double

    Set wsCombined = Worksheets("Combined")
    
    Set mpForm = New ParameterSelection
    With mpForm
    
        .Show
        If Not .Cancel Then
        
            Call FilterPivot("EnrollmentByGrade", .ISDCode, .DistrictCode, .BuildingCode)
            Call FilterPivot("TotalEnrollmentTrend", .ISDCode, .DistrictCode, .BuildingCode)
            Call FilterPivot("PivotTable1", .ISDCode, .DistrictCode, .BuildingCode) 'Race/Ethicity Makeup Trend
            Call FilterPivot("PivotTable2", .ISDCode, .DistrictCode, .BuildingCode) 'Other Demographic Enrollment Trend
        
            Worksheets("Summary").Range("B3").Value = .ISDCode
            Worksheets("Summary").Range("B4").Value = .DistrictCode
            Worksheets("Summary").Range("B5").Value = .BuildingCode
            If .ISDCode = NoSelection Or .DistrictCode = NoSelection Or .BuildingCode = NoSelection Then
            
                Worksheets("Summary").Range("B6:D8").ClearContents
            ElseIf .BuildingCode <> NoSelection Then
                
                mpTotalsRow = Worksheets("EEM").Columns(colEEM.BuildingCode).Find(.BuildingCode, After:=Worksheets("EEM").Cells(1, colEEM.BuildingCode)).Row
                Worksheets("Summary").Range("B6").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.Address)
                Worksheets("Summary").Range("B7").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.City) & " " & _
                                                          Worksheets("EEM").Cells(mpTotalsRow, colEEM.State) & " " & _
                                                          Worksheets("EEM").Cells(mpTotalsRow, colEEM.Zip)
                Worksheets("Summary").Range("B8").Value = Worksheets("EEM").Cells(mpTotalsRow, colEEM.Phone)
            End If
            
            If .BuildingCode <> NoSelection Then
            
                mpTotalsRow = wsCombined.Columns(colCombined.BuildingCode).Find(.BuildingCode, After:=wsCombined.Cells(1, colCombined.BuildingCode)).Row
            ElseIf .DistrictCode <> NoSelection Then
            
                mpTotalsRow = wsCombined.Columns(colCombined.DistrictCode).Find(.DistrictCode, After:=wsCombined.Cells(1, colCombined.DistrictCode)).Row
            Else
            
                mpTotalsRow = wsCombined.Columns(colCombined.ISDCode).Find(.ISDCode, After:=wsCombined.Cells(1, colCombined.ISDCode)).Row
            End If
            
            With wsCombined
            
                mpTotal = .Cells(mpTotalsRow, colCombined.TotalEnrol).Value
                Worksheets("Summary").Range("H3").Value = .Cells(mpTotalsRow, colCombined.American).Value
                If mpTotal <> 0 Then
                    Worksheets("Summary").Range("I3").Value = .Cells(mpTotalsRow, colCombined.American).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I3").Value = 0
                End If
                Worksheets("Summary").Range("H4").Value = .Cells(mpTotalsRow, colCombined.Asian).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I4").Value = .Cells(mpTotalsRow, colCombined.Asian).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I4").Value = 0
                End If
                Worksheets("Summary").Range("H5").Value = .Cells(mpTotalsRow, colCombined.African).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I5").Value = .Cells(mpTotalsRow, colCombined.African).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I5").Value = 0
                End If
                Worksheets("Summary").Range("H6").Value = .Cells(mpTotalsRow, colCombined.Hispanic).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I6").Value = .Cells(mpTotalsRow, colCombined.Hispanic).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I6").Value = 0
                End If
                Worksheets("Summary").Range("H7").Value = .Cells(mpTotalsRow, colCombined.Hawaiian).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I7").Value = .Cells(mpTotalsRow, colCombined.Hawaiian).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I7").Value = 0
                End If
                Worksheets("Summary").Range("H8").Value = .Cells(mpTotalsRow, colCombined.White).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I8").Value = .Cells(mpTotalsRow, colCombined.White).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I8").Value = 0
                End If
                Worksheets("Summary").Range("H9").Value = .Cells(mpTotalsRow, colCombined.TwoOrMore).Value
                If mpTotal <> 0 Then
                   Worksheets("Summary").Range("I9").Value = .Cells(mpTotalsRow, colCombined.TwoOrMore).Value / mpTotal
                Else
                    Worksheets("Summary").Range("I9").Value = 0
                End If
            End With
        End If
    End With
End Sub

[Bob Phillips]

I also use Enums for column ids, so add this code to a standard code module. Put it at the start of the module, before any code procedures that you might have

Public Const NoSelection = "(All)"

Public Enum colCombined
    ISDCode = 2
    ISDName
    DistrictCode = 4
    DistrictName
    BuildingCode = 6
    BuildingName
    TotalEnrol = 9
    American = 12
    Asian
    African
    Hispanic
    Hawaiian
    White
    TwoOrMore
End Enum

Public Enum colEEM
    BuildingCode = 2
    BuildingName
    DistrictCode = 4
    DistrictName
    ISDCode = 6
    ISDName
    Phone = 13
    Address
    City
    State
    Zip
End Enum

[Bob Phillips]

Replace all of the ParameterSelection form code with the following

Private mcCancel As Boolean
Private mcISDCode As Long
Private mcDistrictCode As Long
Private mcBuildingCode As Long
Private mcBuildingRow As Long

Public Property Get Cancel()
    Cancel = mcCancel
End Property

Public Property Get ISDCode()

    With Me.ComboBox1
    
        If .ListIndex = -1 Then
    
            ISDCode = NoSelection
        Else
        
            ISDCode = mcISDCode
        End If
    End With
End Property

Public Property Get DistrictCode()

    With Me.ComboBox2
    
        If .ListIndex = -1 Then
    
            DistrictCode = NoSelection
        Else
        
            DistrictCode = mcDistrictCode
        End If
    End With
End Property

Public Property Get BuildingCode()

    With Me.ComboBox3
    
        If .ListIndex = -1 Then
    
            BuildingCode = NoSelection
        Else
        
            BuildingCode = .List(.ListIndex, 1)
        End If
    End With
End Property

Public Property Get BuildingRow()
    With Worksheets("EEM").Columns(colEEM.BuildingCode)
    
        BuildingRow = .Find(mcBuildingCode, After:=.Cells(1, colEEM.BuildingCode)).Row
    End With
End Property


Private Sub CancelButton_Click()
    mcCancel = True
    Me.Hide
End Sub

Private Sub OKButton_Click()
    mcCancel = False
    Me.Hide
End Sub

Private Sub ComboBox1_Change()
    
    With Me
    
        With .ComboBox1
    
            If .ListIndex <> -1 Then
            
                mcISDCode = .List(.ListIndex, 1)
        
                Set rng = FilterData(colEEM.DistrictName, "=AND(RC[+1]=" & mcISDCode & ",RC[-2]<>R[-1]C[-2])")
            End If
        End With
        
        .ComboBox2.List = Application.Transpose(Application.Transpose(rng))
    End With
End Sub

Private Sub ComboBox2_Change()
    
    With Me
    
        With .ComboBox2
    
            If .ListIndex <> -1 Then
            
                mcDistrictCode = .List(.ListIndex, 1)
        
                Set rng = FilterData(colEEM.BuildingName, "=AND(RC[+1]=" & mcDistrictCode & ",RC[-2]<>R[-1]C[-2])")
            End If
        End With
        
        .ComboBox3.List = Application.Transpose(Application.Transpose(rng))
    End With
End Sub

Private Sub ComboBox3_Change()
    
    With Me
    
        With .ComboBox3
    
            If .ListIndex <> -1 Then
            
                mcBuildingCode = .List(.ListIndex, 1)
            End If
        End With
    End With
End Sub

Private Sub UserForm_Initialize()
Dim rng As Range
    Set rng = FilterData(colEEM.ISDName, "=RC[-2]<>R[-1]C[-2]")
    ComboBox1.List = Application.Transpose(Application.Transpose(rng))
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    
        Cancel = True
        Call CancelButton_Click
    End If
End Sub

Private Function FilterData( _
    KeyColumn As Long, _
    SelectBy As String) As Range
Dim ws As Worksheet
Dim LR As Long
Dim rng As Range
    
    Set ws = Worksheets("EEM")
    With ws
    
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        .Columns(KeyColumn + 1).Insert
        .Cells(1, KeyColumn + 1).Value = "temp"
        .Cells(2, KeyColumn + 1).Resize(LR - 1).FormulaR1C1 = SelectBy
        
        Set rng = .Cells(1, KeyColumn + 1).Resize(LR)
        rng.AutoFilter Field:=1, Criteria1:="=TRUE"
        On Error Resume Next
        Set rng = rng.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    End With
    
    With Worksheets("scratch")
    
        .UsedRange.ClearContents
        rng.Offset(0, -1).Copy .Range("A1")
        rng.Offset(0, -2).Copy .Range("B1")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set FilterData = .Range("A2").Resize(LR - 1, 2)
    End With
    
    ws.Columns(KeyColumn + 1).Delete
End Function

[Bob Phillips]

And finally, add this to a standard code module

Public Function FilterPivot( _
    ByVal PivotTable As String, _
    ByVal ISDCode As String, _
    ByVal DistrictCode As String, _
    ByVal BuildingCode As String) As Boolean
Dim pvtItem As PivotItem

    Application.ScreenUpdating = False
    
    With Worksheets("Working Pivot Tables").PivotTables(PivotTable)
    
        .PivotFields("ISDCode").EnableMultiplePageItems = False
        .PivotFields("DistrictCode").EnableMultiplePageItems = False
        .PivotFields("BuildingCode").EnableMultiplePageItems = False
        
        With .PageRange
        
            .Cells(1, 2).Value = ISDCode
            .Cells(2, 2).Value = DistrictCode
            .Cells(3, 2).Value = BuildingCode
        End With
    End With

    Application.ScreenUpdating = True
End Function

[Bob Phillips]

Last thing, on your Summary worksheet, I would remove gridlines and row and column headers for better visuals. On the Enrollment By Grade and Total Enrollment Trend graphs, I would delete the legends, they add nothing. and on the Race/Ethicity Makeup Trend I would move the legend to the bottom, as per the Other Demographic Enrollment Trend graph. And hide the Field Lists for all of these pivot charts.

[dovermac]

Wow! I'm getting a migraine looking at all this. Ok, first thing, if I replace the calluserform code with yours, will all of your stuff run when I click on the icon in the ribbon? Second, you want me to replace all the parameter selection code with yours? Will this maintain the default selection of everything if nothing is selected or every district if nothing is selected in combobox2? Lastly, I only have 2 modules for the user forms I designed. The first looks like this:

Sub CallUserForm()

ParameterSelection.Show

End Sub

Should I add those 2 sections destined for the standard module and just copy it above the code you have to replace the one line I have for the calluserform?

I can't thank you enough for this really. I am so grateful, you have no idea.

[Bob Phillips]

I haven't changed anything functionally, just added code to handle those selections. As such, after installing of this code, the form will look the same, and they can enter the same data (BTW, the tab order on the form is wrong, it tabs from ISD->District->OK button->Building, it should be Building->OK button). Most of my code kicks in when you click the OK button, although I have also changed the form initialize and combobox1 and combobox2 click events to load the next combobox more efficiently than your original code - which is what I am using the new scratch worksheet for).

The code that I added caters for no District or no Building, assuming all in those cases (I did some checks, but you will need to test to make sure it is all working okay).

Regarding the modules, you can add my extra code (form code excepted) to either of your existing modules, or create extra modules, it will not affect anything. I tend to group my code, so I would have one module all of the global variables, the enums and so on in thread #12 above), with the two form startup procedures (my replacement CallUserForm code in thread #11 above and your existing CallUserForm2 code), and another for helper procedures (the FilterPivot code in thread #14 above).

One other thing I forgot to mention. On your Working Pivot Tables sheet you have 4 pivots that drive the charts on Summary. Three of them just have ISD, District and Building report filters, but the first one, EnrollmentByGrade, has an extra filter on School Year. I think you should remove that as it might unnecessarily restrict the charts.

[dovermac]

Ok, I will start putting your code in and see what happens. One other thing I forgot is the way the data is added into the workbook. The superintendent has to d/l it from a website and it's emailed to them as a CSV file. It's lame I know but I can't change it. I've tried to attach a snapshot, but it keeps giving me errors.


You can see the 3 columns with the "ISD Code, District Code, and Building Code" are not formatted correctly (columns B, D, and F). They want them all 5 characters with leading zeros. Now since this is CSV and the data is not entered that way, I tried formatting these as "custom" since text would not work and copying the files into the macro enabled workbook. The problem as you would imagine is when your code or any tried to move these over to the pivot tables, they go back to the original number of characters. Is there a way to correct that or since the source is CSV, is that not possible? This system is ridiculous imo.

This is what the CallUserform2 calls which is my other user form. This is what I have the User click on to grab the CSV file and place it in the combined tab of the macro-enabled workbook. Is there anything I can do here to somehow automate this conversion so it stays "set in stone."?

Option Explicit

Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Dim strCSV, shtName As String
Dim csvWkbk, wkbk As Workbook
Dim lastRow As Long

'This sets a link to your macro workbook (that you run this from)
Set wkbk = ActiveWorkbook
    
'This gets the CSV file name out of the textbox you stored it in from your OpenButton_Click macro
strCSV = TextBox2.Value
shtName = Me.ComboBox1.Value 'This might need to change, I am not sure if you have to reference the user form first
    
'This opens the CSV workbook
Workbooks.Open strCSV
    
'This sets a link to the CSV workbook
Set csvWkbk = ActiveWorkbook
    
'Find the last row of the sheet you want in your wkbk assuming column A is always populated
lastRow = wkbk.Sheets(shtName).Range("A" & Rows.Count).End(xlUp).Row
    
'This copies the data from the CSV workbook (all cells that are filled in) to the first blank row on the tab in your macro workbook
wkbk.Sheets(shtName).Range(csvWkbk.Sheets(1).UsedRange.Address(0, 0)).Offset(lastRow, 0).Value = csvWkbk.Sheets(1).UsedRange.Value
    
'This closes the csv workbook
csvWkbk.Close
Unload Me
End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub UserForm_Activate()
With Me.ComboBox1
  .Clear
  .AddItem "Combined"
  .AddItem "Count"
  .AddItem "Student Count"
  .AddItem "Finance"
  .AddItem "Accountability"
  .AddItem "EdEval"
  .AddItem "Staffing"
  .ListIndex = 0
End With
End Sub

Private Sub ComboBox1_Change()
End Sub



Private Sub OpenButton_Click()
Dim FileToOpen As Variant
'added to start in the wanted folder
ChDrive "C:\"
ChDir "C:\Dashboard\"
FileToOpen = Application.GetOpenFilename()
If FileToOpen = False Then Exit Sub
TextBox2.Value = FileToOpen
End Sub

[Bob Phillips]

Sorry, I am not quite getting this. I can see that all three codes on Combined are formatted as 5 digits, but the codes are not shown in the pivots, apart from the report filters. But as I understand it, the analysis is done on Summary, and here District and Building are 5 digits. Only ISD isn't, and that could be formatted to 5 digits.

[dovermac]

What I did was took all 5 years, d/l the CSV files, formatted those 3 columns as "custom" and then used my Data Acquisition form (aka CallUserForm2) to upload them in order to the combined tab so they looked correct. My project leader noted that the pivots are not reflecting the combined codes as 5 chars. I think that's the because the data was originally CSV and in many cases only 1 character, not 5. If you want, I could throw one original CSV file so you can see what I'm talking about. I'm just wondering is it possible to go from, for example, a 1 character column under CSV, to a 5 character column that gets "copied" around without losing its formatting? I'm pretty sure my way won't work for an executive.