ParmeetRai
08-29-2023, 02:27 AM
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
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