I've tried two different ways of having the Group and Outline settings
defaulted to the way I want them whenever I open a workbook (I want the
little plus signs to be above and to the left).
Can't get either of them to work.
Attempt #1 - modify the settings thru the command bar:
Sub GroupAndOutline1()
Dim SettingsPopup As CommandBarButton
Set SettingsPopup =
Application.CommandBars(1).Controls(7).Controls(9).Controls(7)
<got stuck here>
End Sub
This got me almost there. I could do SettingsPopup.execute, and the
window would pop up, but I couldn't figure out how to
programatically set the booleans.
Attempt #2 - modify settings via the Outline object
Public Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Application.Worksheets
Debug.Print ws.Name
ws.Outline.SummaryRow = xlAbove
ws.Outline.SummaryColumn = xlLeft
Next ws
End Sub
This seems to work on its own (i.e. the same code in a macro), but when
I use it like so in workbook_open, I get the following error when I
open Excel:
Run-time error "1004".
Method "Worksheets" of object '_Application' failed.
Which seems to me that it's saying that it thinks Worksheets is being
used as a method (although why it's ok when I just call it on its own
is confusing).
Any help with this would be appreciated.
Bookmarks