VBA Form Control to Active X

[davidhall]

I currently have this VBA assigned to a form control button. Can anybody help me write the code to make this an automated active x control.


Sub MyPivot()
'
' MyPivot Macro
'
'
ActiveSheet.PivotTables("PivotTable1").PivotFields("LOAN_NUMBER").CurrentPage = Range("D1").Text
ActiveSheet.PivotTables("PivotTable2").PivotFields("SIFFUL").CurrentPage = Range("D21").Text

End Sub

 

[Smitty]

If that code does what you want, then why change it?

But it's pretty straightforward:

Private Sub CommandButton1_Click
' Your code
End Sub

HTH,

 

[davidhall]

Thanks Smitty.

I am trying to make it so a user doesn't have to click on the button for the pagefield to update.

Cell D1 is linked to another workbook and everything is contingent upon D1. Right now, a user has to click a button for the pagefield to update.

 

[Smitty]

You could use a Worksheet_Calculate event to fire the update code.

 

[davidhall]

Smitty,

It says Run-time erro '1004

Unable to get the PivotTables property of the Worksheet class

Then I choose the option to debug and it's pointing to the highlighted line below.

Private Sub Worksheet_Calculate()
ActiveSheet.PivotTables("PivotTable1").PivotFields("LOAN_NUMBER").CurrentPage = Range("D1").Text
ActiveSheet.PivotTables("PivotTable2").PivotFields("SIFFUL").CurrentPage = Range("D21").Text

End Sub

 

[RoryA]

Assuming that code is in the module for the sheet containing the pivot tables:

Private Sub Worksheet_Calculate()
Me.PivotTables("PivotTable1").PivotFields("LOAN_NUMBER").CurrentPage = Range("D1").Text
Me.PivotTables("PivotTable2").PivotFields("SIFFUL").CurrentPage = Range("D21").Text

End Sub

 

[davidhall]

Thanks Rorya.

How do I get the macro to stop running? When I put in that code it constantly goes...

 

[RoryA]

Oops!

Private Sub Worksheet_Calculate()
On error resume next
Application.EnableEvents = False
Me.PivotTables("PivotTable1").PivotFields("LOAN_NUMBER").CurrentPage = Range("D1").Text
Me.PivotTables("PivotTable2").PivotFields("SIFFUL").CurrentPage = Range("D21").Text
Application.Enableevents = True
End Sub