Set Group and Outline settings...

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.

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?

[email protected] wrote:
>
> 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

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.