Thema: Spalten über UserForm-Auswahl ein- und ausblenden

ClassModule: DieseArbeitsmappe

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim oCtr As CommandBarPopup
   Set oCtr = Application.CommandBars("Worksheet Menu Bar") _
      .FindControl(ID:=30007)
   On Error GoTo ERRORHANDLER
   oCtr.Controls("Spalten").Delete
ERRORHANDLER:
End Sub

Private Sub Workbook_Open()
   Dim oCtr As CommandBarPopup
   Dim oBtn As CommandBarButton
   Set oCtr = Application.CommandBars("Worksheet Menu Bar") _
      .FindControl(ID:=30007)
   On Error Resume Next
   oCtr.Controls("Spalten").Delete
   On Error GoTo 0
   Set oBtn = oCtr.Controls.Add
   With oBtn
      .Caption = "Spalten"
      .OnAction = "DialogAufruf"
      .Style = msoButtonCaption
   End With
End Sub

ClassModule: frmHidden

Private Sub cmdCancel_Click()
   Unload Me
End Sub

Private Sub cmdOK_Click()
   ActiveSheet.Protect
   Unload Me
End Sub

Private Sub lstColumns_Change()
   Dim iCounter As Integer
   For iCounter = 0 To lstColumns.ListCount - 1
      If lstColumns.Selected(iCounter) Then
         Columns(iCounter + 1).Hidden = False
      Else
         Columns(iCounter + 1).Hidden = True
      End If
   Next iCounter
   Range("A1").Select
End Sub

Private Sub UserForm_Initialize()
   ActiveSheet.Unprotect
   lstColumns.Column = Range("A1").CurrentRegion.Rows(1).Value
End Sub

StandardModule: Modul1

Sub DialogAufruf()
   frmHidden.Show
End Sub

Beiträge aus dem Excel-Forum zu den Themen Dialog und ListBox