+ Reply to Thread
Results 1 to 5 of 5

Setting Maximum number of Workbooks per Instance of Excel

  1. #1
    Tom
    Guest

    Setting Maximum number of Workbooks per Instance of Excel

    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



  2. #2
    Bob Phillips
    Guest

    Re: Setting Maximum number of Workbooks per Instance of Excel

    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.

    --
    HTH

    -------

    Bob Phillips
    "Tom" <tvinesAhtNoSpamMecambrianwindows.co.uk> wrote in message
    news:eUYv07i%[email protected]...
    > 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
    >
    >




  3. #3
    Tom
    Guest

    Re: Setting Maximum number of Workbooks per Instance of Excel

    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

    "Bob Phillips" <[email protected]> wrote in message
    news:uVYUsJj%[email protected]...
    > 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.
    >
    > --
    > HTH
    >
    > -------
    >
    > Bob Phillips
    > "Tom" <tvinesAhtNoSpamMecambrianwindows.co.uk> wrote in message
    > news:eUYv07i%[email protected]...
    >> 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
    >>
    >>

    >
    >




  4. #4
    Tom
    Guest

    Re: Setting Maximum number of Workbooks per Instance of Excel

    Ah, found that this works ok.

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

    Cheers


    "Tom" <tvinesAhtNoSpamMecambrianwindows.co.uk> wrote in message
    news:eUYv07i%[email protected]...
    > 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
    >




  5. #5
    Bob Phillips
    Guest

    Re: Setting Maximum number of Workbooks per Instance of Excel

    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



    --
    HTH

    -------

    Bob Phillips
    "Tom" <tvinesAhtNoSpamMecambrianwindows.co.uk> wrote in message
    news:%23C3GFtj%[email protected]...
    > 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
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:uVYUsJj%[email protected]...
    > > 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.
    > >
    > > --
    > > HTH
    > >
    > > -------
    > >
    > > Bob Phillips
    > > "Tom" <tvinesAhtNoSpamMecambrianwindows.co.uk> wrote in message
    > > news:eUYv07i%[email protected]...
    > >> 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
    > >>
    > >>

    > >
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1