Tamalaine
asked on
Why can I run my excel macro directly but not from a button?
I have written a macro, changept, to restructure a pivot table. The macro works when I run it directly from excel(tools> macro, etc) but when I create a button and call changept within the event code I get an error message. Why? Is this just (yet) another example of VBA not handling pivot tables very well or am I doing this the wrong way?
I'm including the code for completeness below but this question is more about the difference between running code from a button and running it directly.
Thanks in advance
sub changept()
ActiveSheet.PivotTables("P ivotTable1 ").PivotSe lect "", xlDataAndLabel
ActiveSheet.PivotTables("P ivotTable1 ").AddFiel ds RowFields:= _
"Selected Price Bands EU", ColumnFields:="Month ", PageFields:=Array( _
"Page Size", "Print Speed Colour", "Mono Speed Band", "Long Product Name", _
"Memory - RAM (MB)", "Print Speed Black (ppm)", "Vendor ", _
"Channel ", "Mono or Colour")
endsub
Private Sub CommandButton3_Click()
changept
End Sub
I'm including the code for completeness below but this question is more about the difference between running code from a button and running it directly.
Thanks in advance
sub changept()
ActiveSheet.PivotTables("P
ActiveSheet.PivotTables("P
"Selected Price Bands EU", ColumnFields:="Month ", PageFields:=Array( _
"Page Size", "Print Speed Colour", "Mono Speed Band", "Long Product Name", _
"Memory - RAM (MB)", "Print Speed Black (ppm)", "Vendor ", _
"Channel ", "Mono or Colour")
endsub
Private Sub CommandButton3_Click()
changept
End Sub
What is the error?
ASKER
Runtime error 1004,Addfields method of PivotTable Class failed!
ASKER
Runtime error 1004,Addfields method of PivotTable Class failed!
ASKER
Runtime error 1004,Addfields method of PivotTable Class failed!
Try this:
sub changept()
ActiveCell.Activate
ActiveSheet.PivotTables("P ivotTable1 ").PivotSe lect "", xlDataAndLabel
ActiveSheet.PivotTables("P ivotTable1 ").AddFiel ds RowFields:= _
...
;)
WoK
sub changept()
ActiveCell.Activate
ActiveSheet.PivotTables("P
ActiveSheet.PivotTables("P
...
;)
WoK
ASKER
I've just discovered that it was apparently an excel bug. I was having the problems on excel 97 but it works fine on 2000! That said, I'd still be interested in knowing why I can run the macro directly but not via the button in excel 97. Is it just down to excel being buggy or are there proper reasons for it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's what I needed. Thanks for everyone's help!