Solved

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

Posted on 2002-03-08
8
472 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
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
What is the error?
0
 

Author Comment

by:Tamalaine
Comment Utility
Runtime error 1004,Addfields method of PivotTable Class failed!
0
 

Author Comment

by:Tamalaine
Comment Utility
Runtime error 1004,Addfields method of PivotTable Class failed!
0
 

Author Comment

by:Tamalaine
Comment Utility
Runtime error 1004,Addfields method of PivotTable Class failed!
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 4

Expert Comment

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

;)
WoK  
0
 

Author Comment

by:Tamalaine
Comment Utility
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
Comment Utility
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
Comment Utility
That's what I needed.  Thanks for everyone's help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now