[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

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
0
Tamalaine
Asked:
Tamalaine
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What is the error?
0
 
TamalaineAuthor Commented:
Runtime error 1004,Addfields method of PivotTable Class failed!
0
 
TamalaineAuthor Commented:
Runtime error 1004,Addfields method of PivotTable Class failed!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

;)
WoK  
0
 
TamalaineAuthor Commented:
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?
0
 
calacucciaCommented:
Tamalaine,

This is a design problem of Excel, and I believe the workaround proposed by WolfgangKoeing is working.

See this article

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q177527
0
 
TamalaineAuthor Commented:
That's what I needed.  Thanks for everyone's help!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now