Example files are available to registered members only... Register and login !

Author Topic: Accessing Ribbon commands from VBA code  (Read 1720 times)

Offline papageno

  • Newbie
  • *
  • Posts: 2
  • Karma: +1/-0
  • SfE user
Accessing Ribbon commands from VBA code
« on: April 13, 2012, 01:52:57 PM »
Hi!

First of all, I'd like to thank you this nice and promising addin. Congratulations!

Well, now going to the business I'm here. I'm trying to use the sparklines in a dynamic context, i.e., taking data from PivotTables coming from OLAP cubes. The main issue in this case is the necessity of changing the height of the ranges of the data. The number of rows is different each run. I send you a sample view.

 The code to refresh the formula works fine. So I'm only a few lines from the final due to the following:

The problem is to redraw the graph each time the PivotTable changes. I need to delete the graph and refresh to take the new data if I change the PivotTable. I do it by hand and no problem after each calculation using Del. Shapes and refresh from the Ribbon. But I'd like the user not to do so.

I'm trying to do if from the code after the PT changes and I'm getting crazy because I'm not able to find the way.

Any help would be nice!

Thanks

Jose
« Last Edit: April 13, 2012, 01:59:35 PM by papageno »

Offline Fabrice

  • Moderator
  • Administrator
  • Sr. Member
  • *****
  • Posts: 135
  • Karma: +18/-0
  • Creator of Sparklines for Excel
    • Sparklines for Excel
Re: Accessing Ribbon commands from VBA code
« Reply #1 on: April 16, 2012, 12:21:14 PM »
Hi papageno,

An example of code to include in your macro after refreshing the PT is :

Private Sub Worksheet_Activate()
Application.EnableEvents = False
   ActiveSheet.PivotTables("PivotTable_name").RefreshTable
    Worksheets("sheet_with_treemaps_charts").Select
    Call DrawCharts
   Worksheets("sheet_with_data").Select "brings focus back on initial sheet
Application.EnableEvents = True
End Sub

For more info, see
http://rimlinger.eu/sparklines/index.php?topic=19.0
Fabrice Rimlinger - Creator of Sparklines for Excel
MacBook Pro Retina
Office 2011 for Mac
Virtual machine : Windows 7 - Office 2013 x32

Offline papageno

  • Newbie
  • *
  • Posts: 2
  • Karma: +1/-0
  • SfE user
Re: Accessing Ribbon commands from VBA code
« Reply #2 on: April 18, 2012, 08:48:22 AM »
Thanks a lot, Fabrice!

I'll try what you suggest and I'll come back with the results and the complete code.

regards,
Jose