Set Group and Outline settings...

[zvonar]

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.

 

[Dave Peterson]

Your code worked for me as is in my test workbook.

Is there any chance that one of the worksheets is protected--or the workbook
itself is shared?

 

[zvonar]

Thanks.

It's possible that's the cause, but I'm not knowingly using protection
(ahem) or sharing.

Some more data points...

- if I just launch Excel, it works fine (in Book1, no error & settings
are as I expect)
- if I close Excel & then open a workbook, I get the error as above

Another thing, I noticed that I was mistaken about how Workbook_Open in
personal.xls works... I thought this was invoked whenever any workbook
was opened, but now I think it's only invoked when Excel is first
opened (I used a msgbox to confirm this). So, I think I need to have
my code somewhere else so that it's invoked whenever I open a new
workbook. Not sure where that is.