ich suche nach einem Code, mit dem ich alle aktiven (sichtbaren) Symbolleisten ausblenden kann. Wie ich sie einzeln anwählen kann weis ich:
Application.CommandBars(x).Visible = False
Wie geht das für alle? Vielen Dank im Voraus...
Gruß, Florian
'-----------------------------------------------
' In ein Modul
Option Explicit
Dim myCollection As New Collection
Dim cb As CommandBar
Sub cbAus()
On Error Resume Next
' Blendet alle Symbolleisten aus
For Each cb In Application.CommandBars
If cb.Visible Then
myCollection.Add cb
cb.Enabled = False
cb.Visible = False
End If
Next cb
End Sub
Sub cbEin()
' Blendet alle Symbolleisten ein
If myCollection.Count Then
For Each cb In myCollection
cb.Enabled = True
cb.Visible = True
Next
End If
End Sub
'-----------------------------------------------
'-----------------------------------------------
' In DieseArbeitsmappe
Option Explicit
Private Sub Workbook_Open()
' Aufruf zum ausblenden
Call Modul1.cbAus
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Aufruf zum einblenden
Call Modul1.cbAus
End Sub
Private Sub Workbook_Activate()
' Aufruf zum ausblenden
Call Modul1.cbAus
End Sub
Private Sub Workbook_Deactivate()
' Aufruf zum einblenden
Call Modul1.cbEin
mfg, GraFri
' Code in DieseArbeitsmappe
Option Explicit
Dim Cn%
Dim CdbList()
Dim Status_FormulaBar As Boolean
Dim Status_HorScroll As Boolean
Dim Status_VerScroll As Boolean
Dim Status_StatusBar As Boolean
Dim Status_Gridlines As Boolean
Dim Status_Headings As Boolean
Dim Status_WorkTabs As Boolean
Private Sub Workbook_Open()
Dim Cdb As CommandBar
'Wenn Titelleiste von Excel geändern werden soll
'Application.Caption = "Meine Mustervorlage"
'falls die eigenen Symbolleisten auch ausgeblendet werden sollen, dann den
'Code 'And Cdb.BuiltIn = True' aus nachstehender Zeile entfernen
Cn = 1
For Each Cdb In Application.CommandBars
If Cdb.Visible And Cdb.Type <> msoBarTypeMenuBar And Cdb.BuiltIn = True Then
ReDim Preserve CdbList(Cn)
CdbList(Cn) = Cdb.Name
Cn = Cn + 1
Cdb.Visible = False
End If
Next
'Stellt den Status fest und blendet alles aus
With ActiveWindow
Status_HorScroll = .DisplayHorizontalScrollBar
If .DisplayHorizontalScrollBar = True Then .DisplayHorizontalScrollBar = False
Status_VerScroll = .DisplayVerticalScrollBar
If .DisplayVerticalScrollBar = True Then .DisplayVerticalScrollBar = False
Status_Gridlines = .DisplayGridlines
If .DisplayGridlines = True Then .DisplayGridlines = False
Status_Headings = .DisplayHeadings
If .DisplayHeadings = True Then .DisplayHeadings = False
Status_WorkTabs = .DisplayWorkbookTabs
If .DisplayWorkbookTabs = True Then .DisplayWorkbookTabs = False
End With
With Application
Status_StatusBar = .DisplayStatusBar
If .DisplayStatusBar = True Then .DisplayStatusBar = False
Status_FormulaBar = .DisplayFormulaBar
If .DisplayFormulaBar = True Then .DisplayFormulaBar = False
'Menüleiste
.CommandBars(1).Enabled = False
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Cdb As CommandBar
Dim Ci%
For Ci = 1 To Cn - 1
Application.CommandBars(CdbList(Ci)).Visible = True
Next Ci
With ActiveWindow
.DisplayHeadings = Status_Headings
.DisplayHorizontalScrollBar = Status_HorScroll
.DisplayVerticalScrollBar = Status_VerScroll
.DisplayGridlines = Status_Gridlines
.DisplayWorkbookTabs = Status_WorkTabs
End With
With Application
.DisplayStatusBar = Status_StatusBar
.DisplayFormulaBar = Status_FormulaBar
.CommandBars(1).Enabled = True
End With
mfg, GraFri
'--------------------------------------------------------------------------------------
' In ein 'Modul'
'
' Menüleiste, Symbolleisten, Bearbeitungsleiste, Statusleiste
' horizontale und vertikale Scrollbalken werden in der 'Tabelle1'
' ausgeblendet, in den übrigen Tabellen eingeblendet.
Option Explicit
Dim StatusBar_Status As Boolean
Dim FormulaBar_Status As Boolean
Dim GitterNetz_Status As Boolean
Dim ScrollHori_Status As Boolean
Dim ScrollVert_Status As Boolean
Dim Cn, n As Integer
Dim Cdb As CommandBar
Dim CdbList()
Sub Auto_Open()
' Wenn die Tabelle "Tabelle1" aktiviert wird, startet das
' Makro "Sub Symbolleiste_Deaktivieren"
Application.Worksheets("Tabelle1").OnSheetActivate = "Symbolleiste_Deaktivieren"
' Wenn die Tabelle "Tabelle1" deaktiviert wird, startet das
' Makro "Sub Symbolleiste_Aktivieren"
Application.Worksheets("Tabelle1").OnSheetDeactivate = "Symbolleiste_Aktivieren"
With Application
.ScreenUpdating = False
' Status der Status- und Eingabeleiste bei Programmstart
StatusBar_Status = .DisplayStatusBar
FormulaBar_Status = .DisplayFormulaBar
' Status der Symbolleisten
Cn = 1
For Each Cdb In .CommandBars
If Cdb.Visible And Cdb.Type <> msoBarTypeMenuBar Then
ReDim Preserve CdbList(Cn)
CdbList(Cn) = Cdb.Name
Cn = Cn + 1
Cdb.Visible = False
End If
Next Cdb
End With
' Status der Gitternetzlinien und der Scrollbars
With ActiveWindow
GitterNetz_Status = .DisplayHeadings
ScrollHori_Status = .DisplayHorizontalScrollBar
ScrollVert_Status = .DisplayVerticalScrollBar
End With
' Menüleiste
CommandBars(1).Enabled = False
End Sub
Sub Auto_Close()
Call Symbolleiste_Aktivieren
End Sub
Sub Symbolleiste_Aktivieren()
Dim Ci%
With Application
' Status- und Eingabeleiste wie bei Programmstart
.DisplayStatusBar = StatusBar_Status
.DisplayFormulaBar = FormulaBar_Status
' Symbolleisten
On Error Resume Next
For Ci = 1 To Cn - 1
.CommandBars(CdbList(Ci)).Visible = True
Next Ci
End With
' Status der Gitternetzlinien und der Scrollbars
With ActiveWindow
.DisplayHeadings = GitterNetz_Status
.DisplayHorizontalScrollBar = ScrollHori_Status
.DisplayVerticalScrollBar = ScrollVert_Status
End With
CommandBars(1).Enabled = True
End Sub
Sub Symbolleiste_Deaktivieren()
Dim Ci%
With Application
' Status- und Eingabeleiste entfernen
If .DisplayStatusBar = True Then .DisplayStatusBar = False
If .DisplayFormulaBar = True Then .DisplayFormulaBar = False
' Symbolleisten
On Error Resume Next
For Ci = 1 To Cn - 1
.CommandBars(CdbList(Ci)).Visible = False
Next Ci
End With
' Status der Gitternetzlinien und der Scrollbars
With ActiveWindow
If GitterNetz_Status = True Then .DisplayHeadings = False
If ScrollHori_Status = True Then .DisplayHorizontalScrollBar = False
If ScrollVert_Status = True Then .DisplayVerticalScrollBar = False
End With
CommandBars(1).Enabled = False
End Sub
mfg, GraFri