title: Excel 2010 VBA Shape Control
body: <p>I've been working on an Excel workbook which contains a number of worksheets which all work in a very similar manner:<br>
  1. They have a PivotTable1<br>
  2. They have a PivotChart1<br>
  3. 4 slicers are used to filter data<br>
  4. And finally I've created shapes, with VBA code behind them to allow me to control the drilldown functionality  </p>
  
  <p>This has taken quite some time, researching various sites for sample code, recording and digesting what's happening with macro's in order to get to where I am. And I'm quite pleased with the current result.  </p>
  
  <p>However, I'm now at a stage where I'd like to ensure the code is as efficient as possible, in order to<br>
  1. Keep the filesize as small as possible<br>
  2. Ensure operating speed is at its best  </p>
  
  <p>So, to my problem.  </p>
  
  <p>I have 4 buttons, which are made with standard Excel shapes. My VBA code for each button looks like this:  </p>
  
  <pre><code>Sub Top10SelectorByValue()
  '
  ' Top10Selector Macro
  '      
  Range("Q15").Select
  '
  ' Clear All Filters
  '
      ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).PivotFields(ActiveCell.PivotField.Name). _
  ClearAllFilters
  '
  ' Apply Top 10 Filter
  '
      ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).PivotFields(ActiveCell.PivotField.Name). _
      PivotFilters.Add Type:=xlTopCount, DataField:=ActiveSheet.PivotTables( _
      ActiveCell.PivotTable.Name).PivotFields("Sum of LineTotalValue"), Value1:=10
  '
  ' Format Shapes
   '
      ActiveSheet.Shapes(Application.Caller).ThreeD.BevelTopType =     IIf(ActiveSheet.Shapes(Application.Caller).ThreeD.BevelTopType = 3, 7, 3)
      ActiveSheet.Shapes("btnTop30").ThreeD.BevelTopType = 3
      ActiveSheet.Shapes("btnTop20").ThreeD.BevelTopType = 3
      ActiveSheet.Shapes("btnSelectAll").ThreeD.BevelTopType = 3
  
      End Sub   
  </code></pre>
  
  <p>As mentioned, I have this code for each of my buttons, which represent Top30, Top20, Top10, and SelectAll options for my customer to click on.  </p>
  
  <p>So here are my 2 questions:<br>
  1. Is it possible to make this code more efficient so that I can set all buttons, with the same styling, except the one clicked, in any other way than I have acheived here.<br>
  2. Is it possible to only have one instance of this code, accessible by all buttons.
  3. I'd like to ensure that at least one option is selected from the 4 buttons, thus leaving it in it's 'down' state</p>
  
  <p>Many thanks, I am a VBA newbie, so please bear with me</p>