Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

I need to by-pass the message "Cannot edit a pivot table in group edit mode'


I am automating a process that send 5 tabs from an Access db macro to an excel template file (.xlsm).  These 5 tabs feed 8 pivot tables in the template.  5 minutes later, my vba script opens the excel template and try to run a macro in the .xlsm template to set up the final report.

My problem is that when the template opens I am getting the message "Cannot edit a pivot table in group edit mode" so when my vba script calls the excel macro nothing happens until I click "OK" to the warning message.

Any ideas of how to get around this message will be greatly appreciatte,





Take the message at face value. Does your code attempt to edit a pivot table? And does this occur in group edit mode? Try to avoid one or the other or both.



The code refreshes different pivot tables from the different tabs imported from Access.  There are 8 pivot tables that feed from 3 tabs.
the group edit mode need to go!
limar18 Feb 26, '18 at 8:14 pm
Refreshing a pivot table isn't the same as editing it. There must be a command in the code which Excel interprets as an edit. Obviously, without looking at the code it won't be possible to determine which one it is. Even then, it will probably require extensive testing. You may not be able to publish the environment for such testing here, and I may not be able to spend the time that would take.
If you are the author of the code go through it line by line and try to develop a suspicion. Step through the code with F8 until you found the line where the message is tripped. Then, perhaps, ask a more detailed question. If you are lucky enough not to be the author let the author do it. He or she is the most qualified for this job.
Variatus (rep: 4889) Feb 26, '18 at 8:36 pm
Add to Discussion

You can try putting this at the top of the macro:

Application.DisplayAlerts = False

and this at the bottom:

Application.DisplayAlerts = True

Be careful though as this stops alerts from appearing while the macro is running, such as closing a file without saving it.



My step is one step before what you suggested.  The problem I have is that in group edit mode no excel macro will run until I manually click ok in the warning message.
What I need is to disable the edit group mode.
limar18 Feb 26, '18 at 8:17 pm
What about using another Excel file to opent he one this one? Then, you can already have the alerts turned off.
don (rep: 1989) Feb 28, '18 at 6:49 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login