Hiding and Showing toolbars

EconSean

Board Regular
Joined
Apr 21, 2002
Messages
129
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top