slang
08-25-2011, 02:38 AM
I have a workbook that I am migrating over to Excel 2010 from 2003. The code below works fine in compatability mode but not when its in XLSM.
Edit, the error I am getting is "Run time Error 5, Invalid proceedure call or argument" in the .current page call.
Let me guess, It all needs to change:motz2:
If it wasn't for wanting to control the @#%$ ribbon I would stay in cmode.
Sub dashboardcatchange()
ActiveSheet.Unprotect Password:="donttouch"
Dim cat As Integer
Application.ScreenUpdating = False
cat = Range("m21").Value
Range("b23").Select
Select Case cat
Case 1
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "G"
Case 2
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "A"
Case 3
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "B"
Case 4
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "C"
Case 5
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "D"
Case 6
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "U"
End Select
Range("C24").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("B15").Select
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="donttouch"
End Sub
Thanks for any help you could provide.:help
Edit, the error I am getting is "Run time Error 5, Invalid proceedure call or argument" in the .current page call.
Let me guess, It all needs to change:motz2:
If it wasn't for wanting to control the @#%$ ribbon I would stay in cmode.
Sub dashboardcatchange()
ActiveSheet.Unprotect Password:="donttouch"
Dim cat As Integer
Application.ScreenUpdating = False
cat = Range("m21").Value
Range("b23").Select
Select Case cat
Case 1
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "G"
Case 2
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "A"
Case 3
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "B"
Case 4
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "C"
Case 5
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "D"
Case 6
ActiveSheet.PivotTables("PivotTable2").PivotFields("Cat").CurrentPage = "U"
End Select
Range("C24").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("B15").Select
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="donttouch"
End Sub
Thanks for any help you could provide.:help