Removing Toolbars via Macro

Gregs

Board Regular
Joined
Sep 6, 2002
Messages
96
Hi Mr Excel,

What is the macro to remove all the toolbars within excel? Also, is there a macro to change the picture of an icon on the toolbar. For example, change the save icon to a smiley face.

Any help would be appreciated.

GREGS
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Emily

Active Member
Joined
Aug 28, 2002
Messages
304
First Part:

With Application.CommandBars
Toolbars(1).Visible = False
Toolbars(2).Visible = False
Toolbars(3).Visible = False
Toolbars(4).Visible = False
Toolbars(5).Visible = False
Toolbars(7).Visible = False
Toolbars("Visual Basic").Visible = False
End With
This message was edited by Emily on 2002-10-04 02:36
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Gregs,

There are plenty of routines kicking around for the hiding of the toolbars. One that I usually use is shown here (from Dave Hawley's site):

<pre>
Hide all of Excels standard Menus and Toolbars and show only your Custom Toolbar.
This code will decide if the user has closed your Workbook or simply Activated another. This code (unless changed) assumes you have a Custom Toolbar called "MyToolBar" which is attached to the Workbook. Whenever the user closes or deactivates the Workbook, all Toolbars and Menubars will be restored as before.

To attach your a Custom Toolbar go to View>Toolbars>Customize-Attach then Copy your Custom Toolbar to the Workbook.


'Module level declaration
Dim IsClosed As Boolean, IsOpen As Boolean

Private Sub Workbook_Activate()
'Show the Custom toolbar
IsClosed = False
If IsOpen = False Then
Application.ScreenUpdating = False
Run "HideMenus"
Application.ScreenUpdating = True
End If
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True
If Cancel = True Then IsClosed = False 'Changed their mind
End Sub


Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
IsOpen = False

On Error Resume Next 'In case it's already gone.
If IsClosed = True Then 'Workbook is closing.
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Delete
End With
Run "ShowMenus"
Else 'They have only activated another Workbook
Run "ShowMenus"
End If
Application.ScreenUpdating = True
End Sub


--------------------------------------------------------------------------------
The code below here must be placed within a Standard Module.
It also assumes you have a hidden sheet with the CodeName of Sheet3.
--------------------------------------------------------------------------------

'Module level declaration
Dim Allbars As CommandBar
Dim i As Integer, BarName As String
Dim FormulaShow As Boolean

Sub HideMenus()
i = 0
Sheet3.Range("C1:C50").Clear
On Error Resume Next
For Each Allbars In Application.CommandBars
If Allbars.Visible = True Then
i = i + 1
With Sheet3
.Cells(i, 3) = Allbars.Name
If Allbars.Name = "Worksheet Menu Bar" Then
Allbars.Enabled = False
Else
Allbars.Visible = False
End If
End With
End If
Next
Application.DisplayFormulaBar = False
With Application.CommandBars("MyToolBar")
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
End With
On Error GoTo 0
End Sub


Sub ShowMenus()
On Error Resume Next
With Sheet3
For i = 1 To WorksheetFunction.CountA(.Columns(3))
BarName = .Cells(i, 3)
Application.CommandBars(BarName).Enabled = True
Application.CommandBars(BarName).Visible = True
Next i
i = 1
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Visible = False
End With
Application.DisplayFormulaBar = True
End With
On Error GoTo 0
Application.CommandBars("Worksheet menu bar").Enabled = True
End Sub</pre>

As regards the smiley face icon - look at the FaceIDs for the various buttons. You may find this link useful:

http://j-walk.com/ss/excel/tips/tip40.htm

HTH
 

Forum statistics

Threads
1,144,363
Messages
5,723,917
Members
422,527
Latest member
JayTheKaz

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
Top