Outline.SummaryColumn = xlRight triggers error if Excel Table is Grouped

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
Using Microsoft Excel 2016 MSO 32-bit from Microsoft Office Professional Plus 2016.

I have a workbook that contains several sheets, one of which holds a structured Excel Table used to record individual investments.
The table includes various data entry columns as well as several calculation/formula columns.
Various sets of columns are Grouped into Column Outlines (i.e. there are several separate column Outlines, but all re at the same level = 2)

The purpose of the sheet_event macro below is to ensure that (irrespective of the user's Excel settings):
(1) the +/- Outline symbols are displayed on the sheet rather than the rows/columns be "hidden"
(2) the Summary Rows and Summary Columns are located ABOVE and RIGHT of the Grouped rows/columns respectively.

Code:
Private Sub Worksheet_Activate()
 
    Dim rowtst As Variant
    Dim coltst As Variant
 
    If ActiveWindow.DisplayOutline = False Then ActiveWindow.DisplayOutline = True
 
    rowtst = ActiveSheet.Rows.OutlineLevel
    coltst = ActiveSheet.Columns.OutlineLevel
 
    If IsNull(rowtst) Then ActiveSheet.Outline.SummaryRow = xlAbove
    If IsNull(coltst) Then ActiveSheet.Outline.SummaryColumn = xlRight
 
End Sub

However, whenever any sheet holds an Excel Table and any of the rows and/or columns within that Table are Grouped, the last two code lines trigger the following errors, as applicable to the Outline axis:
“Run-time error 1004: Unable to set the SummaryRow property of the Outline class.”
“Run-time error 1004: Unable to set the SummaryColumn property of the Outline class.”


Note that if “normal” spreadsheet rows or columns (i.e. outside an Excel Table) are Grouped then the error does NOT occur.

Is anyone able to explain what’s going on (wrong) here and how I can overcome this problem (if possible)?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top