Toolbar Reactivation

[Guest]

Below is the code for a module I have based on Steven Roman's "Writing Excel
Macros with VBA". I now need to have a module that will take the data stored
in sheet3 and enable the toolbars I've disabled here.

Can I get some help on how I would read this data from sheet3 and enable the
toolbars?

Thanks in advance.

Hal


Option Explicit

Public Sub ListCmdBars()
Dim sType As String, cbar As CommandBar, rng As Range
Dim cbarCount As Integer, lRow As Long, lCol As Long
lRow = 2
lCol = 2
Sheets("Sheet3").Cells(lRow - 1, lCol - 1) = "Name" 'cbar.Name
Sheets("Sheet3").Cells(lRow - 1, lCol) = "Type" 'sType
Sheets("Sheet3").Cells(lRow - 1, lCol + 1) = "Visible" 'cbar.Visible

For Each cbar In Application.CommandBars
Select Case cbar.Type
Case msoBarTypeNormal 'A toolbar
sType = "Normal"
Case msoBarTypeMenuBar 'A menu bar
sType = "Menu Bar"
Case msoBarTypePopup 'Menu, Submenu
sType = "Popup"
End Select

If cbar.Visible = True Then
Sheets("Sheet3").Cells(lRow, lCol - 1) = cbar.Name
Sheets("Sheet3").Cells(lRow, lCol) = sType
Sheets("Sheet3").Cells(lRow, lCol + 1) = cbar.Visible
cbar.Enabled = False
lRow = lRow + 1
End If
Next

End Sub

 

[Jim Cone]

Hal,

If you have disabled all of the command bars then maybe you should
just go thru and enable all of them...

For Each cbar In Application.CommandBars
cbar.Enabled = True
Next

You can restore all of the menu/toolbars to their default configuration
by first closing Excel and then deleting the file that stores them.
Excel will automatically recreate the menus/toolbars when Excel is restarted.
In xl2002 this file is named Excel10.xlb. The file name varies slightly
from version to version but always ends with .xlb.

In general, it is not good practice to take away all of the command bars
from the user, because all other open workbooks also lose their command bars.
It becomes very inconvenient for the user.

To answer your specific question...

Dim cbarName As Excel.Range
Dim x As Long
x = 2
Set cbarName = Worksheets("Sheet3").Cells(x, 1)
Do Until Len(cbarName.Value) = 0
Application.CommandBars(cbarName.Value).Enabled = True
x = x + 1
Set cbarName = Worksheets("Sheet3").Cells(x, 1)
Loop
Set cbarName = Nothing
'-----------------------------------

One more note, it might be better to just change the .Visible property of
certain command bars instead of disabling all of them.

Regards,
Jim Cone
San Francisco, USA


"Hal" <[email protected]>
wrote in message
Below is the code for a module I have based on Steven Roman's "Writing Excel
Macros with VBA". I now need to have a module that will take the data stored
in sheet3 and enable the toolbars I've disabled here.
Can I get some help on how I would read this data from sheet3 and enable the toolbars?
Thanks in advance.
Hal


Option Explicit

Public Sub ListCmdBars()
Dim sType As String, cbar As CommandBar, rng As Range
Dim cbarCount As Integer, lRow As Long, lCol As Long
lRow = 2
lCol = 2
Sheets("Sheet3").Cells(lRow - 1, lCol - 1) = "Name" 'cbar.Name
Sheets("Sheet3").Cells(lRow - 1, lCol) = "Type" 'sType
Sheets("Sheet3").Cells(lRow - 1, lCol + 1) = "Visible" 'cbar.Visible

For Each cbar In Application.CommandBars
Select Case cbar.Type
Case msoBarTypeNormal 'A toolbar
sType = "Normal"
Case msoBarTypeMenuBar 'A menu bar
sType = "Menu Bar"
Case msoBarTypePopup 'Menu, Submenu
sType = "Popup"
End Select
If cbar.Visible = True Then
Sheets("Sheet3").Cells(lRow, lCol - 1) = cbar.Name
Sheets("Sheet3").Cells(lRow, lCol) = sType
Sheets("Sheet3").Cells(lRow, lCol + 1) = cbar.Visible
cbar.Enabled = False
lRow = lRow + 1
End If
Next
End Sub

 

[Guest]

Thanks for your help here Jim.

Point well taken about the potential inconvenience here. I would have liked
to use the line cbar.Visible = False but that usage was not allowed so I went
with the cbar.Enbled = False, as shown below. Being the novice I am, I
haven't been able to figure out how to use the data collected in Sheet3 to
just make the menus and bars invisable. The whole reason for doing this in
the first place is I don't want the user to have access to the functions of
this file. They just need to run it and let it take care of its specific
functions.

Regards,
Hal