Setting Maximum number of Workbooks per Instance of Excel

[Tom]

Hi All,

I have a workbook that hides all the control bars to prevent the user from
messing about with it. When it was opened though, if they have any workbooks
already open, they'd loose all the toolbars associated with it. So I have
made an opener for it - a separate workbook that creates a new instance of
excel and opens the main workbook into that. Fine - however any workbooks
opened after that are opened into the main workbooks instance of excel -
meaning that they will loose all of their toolbars again.

So how can I set the second instance to allow only the one workbook to be
opened?

Cheers,

Tom

 

[Bob Phillips]

Tom,

How about an alternative approach?

When your workbook is activated, save all the toolbars, and then setup as
you want. When it is de-activated, restore them all.

 

[Tom]

Cheers for the fast response.

I tried a
for each c in application.controlbars
c.visible = false
next

and it came up with a runtime error 80004005 - method 'visible' of object
'command bar' failed.

What property should i use to determine which commandbars are hideable.

Tom

 

[Tom]

Ah, found that this works ok.

if c.Protection = 0 And c.Enabled = True

Cheers

 

[Bob Phillips]

Tom,

That is probably because of Worksheet Menu Bar, which doesn't follow the
rule. I would use something like (untested)

Dim aryToolbars

Sub hideBars()

Redim aryToolbars(1 To Application.Commandbars.Count)
For Each c In Application.Commandbars
If c.Name = "Worksheet Menu Bar" then
If c.Enabled Then
c.Enabled = False
aryToolbars(c.Index) = True
Else
aryToolbars(c.Index) = False
EndIf
Else
If c.VisibleThen
c.Visible= False
aryToolbars(c.Index) = True
Else
aryToolbars(c.Index) = False
EndIf
End If
Next c

End Sub

Sub restoreBars()

if aryToolb ars(1) Then Application.commandbars(1).enabled = true
For i = 2 To ubound(aryToolbars)
If aryToolbars(i) Then
Application.commandbars(i).Visible = True
End If
Next i