Consulting

Results 1 to 4 of 4

Thread: VBAcode to switch(show and hide) between 4 sum value fields on a data mod pivot table

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Question VBAcode to switch(show and hide) between 4 sum value fields on a data mod pivot table

    I have2 buttons(Switch between Actuals/Forecast) and (Switch between Cost/Hours). Pivot table named Pivot_Dashboard is a data model pivot and is pricing data from Actualandforecast table. Below is my entire code and it is working to show the pivot sum value field but it does not hide the previous field as it should be. For example. If I select Actual/Forecast it should show Actual and hide Forecast and vice versa. But currently it adds the columns to show the actual and doesn't hide the Forecast or vice versa. Hoping to get help as soon as possible.
    Thanks!

    Option Explicit
    Dim col_Field As String
    
    Sub Cost_Button()
        ' CostHours_Button Macro
        If Worksheets("HCV_Financials").Range("D10").Value = "Actual Hour" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Actual Cost"col_Field = "Actual Labor Cost"
        ElseIf Worksheets("HCV_Financials").Range("D10").Value = "Actual Cost" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Actual Hour"
             col_Field = "Actual Labor Hours"
        ElseIf Worksheets("HCV_Financials").Range("D10").Value = "Forecast Hour" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Forecast Cost"
             col_Field = "Forecast Cost"
        ElseIf Worksheets("HCV_Financials").Range("D10").Value = "Forecast Cost" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Forecast Hour"
             col_Field = "Forecast Hours"
        Else
             MsgBox ("Incorrect Value in D10")
        End If
        Call Change_Value
    End Sub
    
    Sub Forecast_Button()
        ' ActualForecast_Button Macro
        If Worksheets("HCV_Financials").Range("D10").Value = "Actual Hour" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Forecast Hour"
             col_Field = "Forecast Hours"
        ElseIf Worksheets("HCV_Financials").Range("D10").Value = "Actual Cost" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Forecast Cost"
             col_Field = "Forecast Cost"
        ElseIf Worksheets("HCV_Financials").Range("D10").Value = "Forecast Hour" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Actual Hour"
             col_Field = "Actual Labor Hours"
        ElseIf Worksheets("HCV_Financials").Range("D10").Value = "Forecast Cost" Then
             Worksheets("HCV_Financials").Range("D10").Value = "Actual Cost"
             col_Field = "Actual Labor Cost"
        Else
             MsgBox ("Incorrect Value in D10")
        End If
        Call Change_Value
    End Sub
    
    Sub Change_Value()
        'To change the value between Sum Of Actual Hours, Sum of Actual Cost, Sum of Forecast Hours, Sum of Forecast Cost
        Dim pt As PivotTable
        Dim cf As CubeField
        Dim i As Integer
        Dim s As String
        Set pt = ActiveSheet.PivotTables("Pivot_Dashboard")
        i = 1
        Do Until Worksheets("HCV_Financials").Range("P12").Value = "" Or i > 100
             If Worksheets("HCV_Financials").Range("P12").Value <> "" Then
                 If Not IsNumeric(Worksheets("HCV_Financials").Range("P12").Value) Then
                     s = Worksheets("HCV_Financials").Range("P12").Value
                     ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields.PivotFields(s).Orientation = xlHidden
                 End If
             End If
             i = i + 1
        Loop
        If col_Field = "Actual Labor Cost" Then
             ActiveSheet.PivotTables("Pivot_Dashboard").AddDataField ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[Measures].[Sum of Actual Labor Cost]"), _
             "Sum of Actual Labor Cost"
             ActiveSheet.PivotTables("Pivot_Dashboard").PivotFields("[Measures].[Sum of Actual Labor Cost]").NumberFormat = "$#,##0;[Red]$#,##0"
             If Worksheets("HCV_Financials").Range("P12").Value <> "" ThenIf Not IsNumeric(Worksheets("HCV_Financials").Range("P12").Value) Then
                 s = Worksheets("HCV_Financials").Range("P12").Value
                 ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[ActualAndForecast].[Actual Labor Cost]").Orientation = xlHidden
             End If
        End If
        ElseIf col_Field = "Actual Labor Hours" Then
             ActiveSheet.PivotTables("Pivot_Dashboard").AddDataField ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[Measures].[Sum of Actual Labor Hours]"), _
             "Sum of Actual Labor Hours"
             If Worksheets("HCV_Financials").Range("P12").Value <> "" Then
                 If Not IsNumeric(Worksheets("HCV_Financials").Range("P12").Value) Then
                     s = Worksheets("HCV_Financials").Range("P12").Value
                     ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[ActualAndForecast].[Actual Labor Hours]").Orientation = xlHidden
                 End If
             End If
        ElseIf col_Field = "Forecast Cost" Then
             ActiveSheet.PivotTables("Pivot_Dashboard").AddDataField ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[Measures].[Sum of Forecast Cost]"), _
             "Sum of Forecast Cost"
             ActiveSheet.PivotTables("Pivot_Dashboard").PivotFields("[Measures].[Sum of Forecast Cost]").NumberFormat = "$#,##0;[Red]$#,##0"
             If Worksheets("HCV_Financials").Range("P12").Value <> "" Then
                 If Not IsNumeric(Worksheets("HCV_Financials").Range("P12").Value) Then
                     s = Worksheets("HCV_Financials").Range("P12").Value
                     ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[ActualAndForecast].[Forecast Cost]").Orientation = xlHidden
                 End If
             End If
        ElseIf col_Field = "Forecast Hours" Then
             ActiveSheet.PivotTables("Pivot_Dashboard").AddDataField ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[Measures].[Sum of Forecast Hours]"),  _
             "Sum of Forecast Hours"
             If Worksheets("HCV_Financials").Range("P12").Value <> "" Then
                 If Not IsNumeric(Worksheets("HCV_Financials").Range("P12").Value) Then
                     s = Worksheets("HCV_Financials").Range("P12").Value
                     ActiveSheet.PivotTables("Pivot_Dashboard").CubeFields("[ActualAndForecast].[Forecast Hours]").Orientation = xlHidden
                 End If
             End If
        End If
    End Sub
    Last edited by Aussiebear; 11-27-2024 at 07:35 PM. Reason: Taken out the extra whitespace and added code tags

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •