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

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!

Code:

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

Welcome to VBAX ParmeetRai. You will notice that in this forum we try to wrap our code with code tags ( see the first line in my signature). Secondly there is no need to write to every alternative line when submitting the code. The extra whitespace just makes your code harder to read. Its all good, as I've edited the code to make it more suitable to read ( and understand), I hope you don't mind.

Thanks much for your response. This is my first time ever joining a forum. Thanks for the helpful tip.

Just a quick question here, in the Sub Change Value, the logic doesn't seem right. You have a series of Elseif's but no If to start the looping. Can you check this please?