Link to home
Start Free TrialLog in
Avatar of Tamalaine
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("PivotTable1").PivotSelect "", xlDataAndLabel
ActiveSheet.PivotTables("PivotTable1").AddFields 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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

What is the error?
Avatar of Tamalaine
Tamalaine

ASKER

Runtime error 1004,Addfields method of PivotTable Class failed!
Runtime error 1004,Addfields method of PivotTable Class failed!
Runtime error 1004,Addfields method of PivotTable Class failed!
Try this:
sub changept()
ActiveCell.Activate
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
...

;)
WoK  
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
Avatar of calacuccia
calacuccia
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's what I needed.  Thanks for everyone's help!