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

[ParmeetRai]

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

[Aussiebear]

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.

[ParmeetRai]

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

[Aussiebear]

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?