Excel und VBA: Formeln, Programmierung, Lösungen

ClassModule: DieseArbeitsmappe

Private Sub Workbook_Open()
   Dim oBar  As CommandBar
   Dim iRow As Integer
   For Each oBar In Application.CommandBars
      If oBar.Visible And oBar.Type <> _
         msoBarTypeMenuBar Then
            iRow = iRow + 1
            With ThisWorkbook.Worksheets("CmdBars")
               .Cells(iRow, 1).Value = oBar.Name
               oBar.Visible = False
            End With
        End If
    Next oBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Call CmdBarsEin
End Sub

StandardModule: basMain

Sub WksAus()
   Worksheets("CmdBars").Visible = xlVeryHidden
End Sub

Sub CmdBarsEin()
   Dim iRow As Integer
   iRow = 1
   With ThisWorkbook.Worksheets("CmdBars")
      Do Until IsEmpty(.Cells(iRow, 1))
         Application.CommandBars(.Cells(iRow, 1) _
            .Value).Visible = True
         iRow = iRow + 1
      Loop
      .Columns(1).Clear
   End With
End Sub

    

Beiträge aus dem Excel-Forum zu den Themen Ereignis und Open