Solved

Why can I run my excel macro directly but not from a button?

Posted on 2002-03-08
8
477 Views
Last Modified: 2010-07-27
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
Comment
Question by:Tamalaine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6850027
What is the error?
0
 

Author Comment

by:Tamalaine
ID: 6850054
Runtime error 1004,Addfields method of PivotTable Class failed!
0
 

Author Comment

by:Tamalaine
ID: 6850084
Runtime error 1004,Addfields method of PivotTable Class failed!
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Tamalaine
ID: 6850094
Runtime error 1004,Addfields method of PivotTable Class failed!
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
ID: 6850113
Try this:
sub changept()
ActiveCell.Activate
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
...

;)
WoK  
0
 

Author Comment

by:Tamalaine
ID: 6850121
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
 
LVL 17

Accepted Solution

by:
calacuccia earned 100 total points
ID: 6850528
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
 

Author Comment

by:Tamalaine
ID: 6850678
That's what I needed.  Thanks for everyone's help!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question