Identifying toolbars.

[Ben]

I have wriiten a few lines of code that cause Excel to switch to full screen
and to eliminate the toolbars and formula bar etc when the sheet is activated
.. On deactivation I also have a macro to restore the screen back to normal
view along with the toolbars that I normally use. My question is: If I am
applying this code on another person's computer how can I determine what
toolbars were visible prior to the sheet activation macro so that I can
restore them on de-activation.
Thank you

 

[Nigel]

Store the toolbars on a hidden worksheet as the application opens and then
restore them as the application closes. The following code stores toolbars
in sheet codename shTBsheet. Call this code from the open workbook event.
The next sub restores the toolbars based on those previously saved, call
this code from the before close workbook event. You can use a similar
process to record the current options, if they need to be changed.

Sub HideAllToolBars()
Dim TB As CommandBar
Dim TBNum As Integer
' record the toolbars visible in excel on load
' then disable ALL toolbars
shTBsheet.Cells.Clear
TBNum = 0
For Each TB In CommandBars
If TB.Type = msoBarTypeNormal Then
If TB.Visible Then
TBNum = TBNum + 1
TB.Visible = False
shTBsheet.Cells(TBNum, 1) = TB.Name
End If
TB.Enabled = False
End If
Next TB
End Sub

Sub RestoreToolBars()
Dim xlr As Integer, xr As Integer
Application.ScreenUpdating = False
With shTBsheet
xlr = .Cells(Rows.Count, 1).End(xlUp).Row
For xr = 1 To xlr
CommandBars(.Cells(xr, 1).Value).Enabled = True
CommandBars(.Cells(xr, 1).Value).Visible = True
Next xr
End With
End Sub

 

[Bob Phillips]

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[JLGWhiz]

I use this code in a program I have and it takes away then restores the
peripheral controls without changing any settings.

To hide:
With ActiveWindow
.DisplayHorizontalScrollBar = False 'Make space and clear screen
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayHeadings = False
.DisplayGridlines = False
End With

To restore:
With ActiveWindow 'This with statement sets everything to normal
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
.DisplayGridlines = True
End With

 

[Tom Ogilvy]

without changing any settings

that would be true if they had all those peripheral controls set to true to
start with, but that isn't always the case. Think you would need to store
the current settings and then restore them.

 

[Robin]

Hi Nigel
I ran this code and the problem is that the toolbars that are available
after running the restore macro are limited only to the ones that were
initially selected (i.e. all the other toolbars such as control toolbox,
drawing are no longer shown) and I can't find a way of getting them back in
the view/toolbars menu (!) Please help.
Cheers
Rob