Results 1 to 8 of 8
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    removing all tool bars (Excel 2003 (all))

    I'd like to ask for recommendations on the best way to remove all menu bars from a workbook instance. I also want to restore whatever tool bars another workbook had opened/visible so that if they already have Excel open and then open my workbook, I want to capture a list of their visible tool bars (and state of status bar, tabs on/off, etc.) and restore that same configuration when they either close my workbook or deactivate it to few a different workbook.

    I don't want to assume they have certain tool bars open (or status bar is visible, or tabs on, etc.) so is there a way to capture all of these settings and gracefully restore them so the user doesn't get mad at me (my workbook) for turning things off that they set in their Excel application?

    My goal for the Excel app I'm writing is to remove all Excel-isms from the UI but I don't want to wipe out any other workbook's settings.

    Thnx,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: removing all tool bars (Excel 2003 (all))

    This is tricky - if things go wrong, the user may end up with all toolbars hidden; that won't make you popular. You could use the following code:

    Public col As New Collection

    Sub StoreToolbars()
    Dim cbr As CommandBar
    On Error Resume Next
    For Each cbr In Application.CommandBars
    If cbr.Visible Then
    col.Add cbr.Name
    End If
    Next cbr
    Set cbr = Nothing
    End Sub

    Sub ShowToolbars(Flag As Boolean)
    Dim itm As Variant
    On Error Resume Next
    For Each itm In col
    Application.CommandBars(itm).Visible = Flag
    Next itm
    End Sub

    Run StoreToolbars when you open your workbook. To hide toolbars (for example in the Workbook_Activate event), execute

    ShowToolbars False

    and to show them again (for example in the Workbook_Deactivate event), execute

    ShowToolbars True

    The code contains On Error Resume Next to prevent errors from clearing the collection. For example, it is not possible to hide the menu bar. It's not very elegant, but otherwise, you'll have to write a lot of error handling.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: removing all tool bars (Excel 2003 (all))

    This is a great start, thanks. What about the fields in Tools/Options, aren't those workbook specific? So if I turn off worksheet tabs, do I have to restore those for the other workbooks? It seems some of these menu items are for the current workbook (even worksheet like gridlines) but others are more global. Is there a way to detect what's sheet-level vs. window-level vs. workbook-level vs. application-level, or is this just diving down one big, dark hole that no sane person should go?

    This app really doesn't need much of Excel but it's a free GUI that I know how to program whereas if I did it in C++ or VB I'd have more work to do (plus it's been 3-5 years since I've done any programs in those languages).

    Thnx,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: removing all tool bars (Excel 2003 (all))

    The settings for the visibility of the Formula Bar and Status Bar are application-wide, so you should store their values in global variables and restore them when you're done.
    The settings in the lower part of the View tab of Tools | Options are window-specific. You can change them in your workbook without affecting other workbooks.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: removing all tool bars (Excel 2003 (all))

    You can adapt the code in <post#=343644>post 343644</post#> to persist data out to (and read the data back from) an XML file if there is a danger that the global variables might get lost without the toolbars first being restored.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: removing all tool bars (Excel 2003 (all))

    Hi,

    I wrote this once, it stores things to a textfile that is deleted when the application has successfully restored things.
    When (at startup) the app discoveres there is still a leftover file on disk, it does not save the current state, as it may well be everything is still disabled.
    This ensures that when something went wrong, the next time the app is closed it may still restore things as they were.
    <pre>Sub HideCBs()
    Dim oBar As CommandBar
    Dim lCount As Long
    Dim bSave As Boolean
    Dim lFile As Long
    Dim sFilename As String
    Dim bFormula As Boolean
    Dim bEnabled As Boolean
    Dim bVisible As Boolean
    Dim bDisplayHorizontalScrollBar As Boolean
    Dim bDisplayOutline As Boolean
    Dim bDisplayScrollBars As Boolean
    Dim bDisplayStatusBar As Boolean
    Dim bDisplayVerticalScrollBar As Boolean
    Dim bDisplayWorkbookTabs As Boolean
    RemoveControlMenu
    With ActiveWindow
    bDisplayOutline = .DisplayOutline
    bDisplayWorkbookTabs = .DisplayWorkbookTabs
    bDisplayHorizontalScrollBar = .DisplayHorizontalScrollBar
    bDisplayVerticalScrollBar = .DisplayVerticalScrollBar
    End With
    With Application
    bDisplayStatusBar = .DisplayStatusBar
    bDisplayScrollBars = .DisplayScrollBars
    bFormula = .DisplayFormulaBar
    End With
    On Error Resume Next
    With Application
    .DisplayFormulaBar = False
    .DisplayScrollBars = False
    .DisplayStatusBar = False
    End With
    sFilename = ThisWorkbook.Path & "Commandbars.ini"
    If Dir(sFilename) = "" Then
    bSave = True
    lFile = FreeFile
    Open sFilename For Output As lFile
    Write #lFile, "Formulabar", bFormula
    Write #lFile, "DisplayOutline", bDisplayOutline
    Write #lFile, "DisplayWorkbookTabs", bDisplayWorkbookTabs
    Write #lFile, "DisplayScrollBars", bDisplayScrollBars
    Write #lFile, "DisplayStatusBar", bDisplayStatusBar
    Write #lFile, "DisplayHorizontalScrollBar", bDisplayHorizontalScrollBar
    Write #lFile, "DisplayVerticalScrollBar", bDisplayVerticalScrollBar
    End If
    On Error Resume Next
    For Each oBar In Application.CommandBars
    bEnabled = oBar.Enabled
    bVisible = oBar.Visible
    If bSave Then
    Write #lFile, oBar.Name, bEnabled, bVisible
    End If
    oBar.Visible = False
    oBar.Enabled = False
    Next
    ' Application.DisplayFullScreen = True
    Application.CommandBars("IMKViewmenu").Enabled = True
    Application.CommandBars("IMKViewmenu").Visible = True
    With ActiveWindow
    .DisplayHeadings = False
    .DisplayOutline = False
    .DisplayWorkbookTabs = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = True
    End With
    On Error GoTo 0
    If bSave Then
    Close #lFile
    End If
    End Sub

    Sub ShowCBs()
    Dim oBar As CommandBar
    Dim lFile As Long
    Dim sFilename As String
    Dim sTemp As String
    Dim bFormula As Boolean
    Dim bEnabled As Boolean
    Dim bVisible As Boolean
    Dim bDisplayHorizontalScrollBar As Boolean
    Dim bDisplayOutline As Boolean
    Dim bDisplayScrollBars As Boolean
    Dim bDisplayStatusBar As Boolean
    Dim bDisplayVerticalScrollBar As Boolean
    Dim bDisplayWorkbookTabs As Boolean
    RestoreControlMenu
    lFile = FreeFile
    sFilename = ThisWorkbook.Path & "Commandbars.ini"
    If Dir(sFilename) = "" Then
    'Settings verdwenen, op z'n minst e.e.a. weer bruikbaar maken
    For Each oBar In Application.CommandBars
    oBar.Enabled = True
    Next
    Exit Sub
    End If
    Open sFilename For Input As lFile
    Input #lFile, sTemp, bFormula
    Input #lFile, sTemp, bDisplayOutline
    Input #lFile, sTemp, bDisplayWorkbookTabs
    Input #lFile, sTemp, bDisplayScrollBars
    Input #lFile, sTemp, bDisplayStatusBar
    Input #lFile, sTemp, bDisplayHorizontalScrollBar
    Input #lFile, sTemp, bDisplayVerticalScrollBar
    With ActiveWindow
    .DisplayOutline = bDisplayOutline
    .DisplayWorkbookTabs = bDisplayWorkbookTabs
    .DisplayHorizontalScrollBar = bDisplayHorizontalScrollBar
    .DisplayVerticalScrollBar = bDisplayVerticalScrollBar
    End With
    With Application
    .DisplayStatusBar = bDisplayStatusBar
    .DisplayScrollBars = bDisplayScrollBars
    .DisplayFormulaBar = bFormula
    End With
    On Error Resume Next
    For Each oBar In Application.CommandBars
    Input #lFile, sTemp, bEnabled, bVisible
    If oBar.Name = sTemp Then
    oBar.Enabled = bEnabled
    oBar.Visible = bVisible
    End If
    Next
    Close #lFile

    'To be on the safe side, at least enable the worksheet menubar!
    With Application.CommandBars(1)
    .Enabled = True
    .Visible = True
    End With

    Kill sFilename
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: removing all tool bars (Excel 2003 (all))

    One simple way may be to use the Workbook or Worksheet Activate Events to show the application in full screen eg
    Private Sub Workbook_Activate()
    Application.DisplayFullScreen = True
    End Sub

    Private Sub Workbook_Deactivate()
    Application.DisplayFullScreen = False
    End Sub

    This doesn't change the user settings
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: removing all tool bars (Excel 2003 (all))

    That's perfect! Simple, clean, easy, I like it. <img src=/S/clever.gif border=0 alt=clever width=15 height=15> I never used full screen mode and so didn't even remember it exists. I'll turn it off when they change workbooks and turn it on when my workbook is active. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Thnx, Deb

Posting Permissions

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