Hiding and Showing toolbars

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Hiding and Showing toolbars

  1. #1
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Forgive me if this has been covered, but I couldn't find it referenced anywhere, and the problem is making me a little crazy.

    Ok, I want to hide toolbars during the Auto_Open sub, which is easy enough to do. I want to put them back during the Auto_Close sub, which again, is easy enough to do.

    My problem is this: suppose the user has a toolbar already hidden (this is how he or she wants Excel set up) before my program runs. Now, if I try to hide something that is already hidden (via my Auto_Open sub), there is no problem, the VBA routine chugs along happily. The trouble comes at the Auto_Close sub; I may unhide something that should be left hidden (at least as far as this particular user is concerned).

    Thus, I want to leave Excel set up exactly as it was before the user started my program.

    Any thoughts on how to best accomplish this? Please let me know if I have done a poor job describing my dilemma and I will try to explain differently.

    Thanks in advance for your suggestions.

    Regards,

    Sean C.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Sean,

    What you can do is run a macro which stores all of the users visible toolbars before you hide them. E.g. this code will list all of the visible toolbars on a sheet called Toolbars (which you will probably want to keep hidden):-

    Code:
    Sub StoreToolbars()
    Dim comBar As CommandBar, lngRow As Long
    
    lngRow = 1
    Sheets("Toolbars").Range("A1:A65536").ClearContents
    For Each comBar In Excel.CommandBars
        If comBar.Visible = True Then
            Sheets("Toolbars").Cells(lngRow, 1) = comBar.Name
            lngRow = lngRow + 1
        End If
    Next comBar
    
    End Sub
    Now, once this code has run you can hide all of the toolbars you want to hide. Once you want to restore the previous settings use something like this:-

    Code:
    Sub UnhideBars()
    Dim lngRow As Long
    Dim strToolbarName As String
    For lngRow = 1 To Sheets("Toolbars").UsedRange.Rows.Count
        strToolbarName = Sheets("Toolbars").Cells(lngRow, 1)
        Excel.CommandBars(strToolbarName).Visible = True
    Next lngRow
    End Sub
    HTH,

    Dan

  3. #3
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks Dan, that was very handy.

    Regards,

    Sean

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com