Gruppe
Dialog
Problem
Wie kann ich das Ein- und Ausblenden von Monats-Arbeitsblättern über UserForm-ComboBoxes steuern? Ein Datenblatt soll nur nach Passworteingabe geöffnet werden können.
ClassModule: DieseArbeitsmappe
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oExtras As CommandBarPopup
Set oExtras = Application.CommandBars( _
"Worksheet Menu Bar").FindControl(ID:=30007)
On Error Resume Next
oExtras.Controls("Blattauswahl").Delete
On Error GoTo 0
End Sub
Private Sub Workbook_Open()
Dim oExtras As CommandBarPopup
Dim oBtn As CommandBarButton
Set oExtras = Application.CommandBars( _
"Worksheet Menu Bar").FindControl(ID:=30007)
On Error Resume Next
oExtras.Controls("Blattauswahl").Delete
On Error GoTo 0
Set oBtn = oExtras.Controls.Add
With oBtn
.Caption = "Blattauswahl"
.OnAction = "CallForm"
.BeginGroup = True
.Style = msoButtonCaption
End With
End Sub
ClassModule: frmPassword
Private Sub cmdOK_Click()
If txtPassword.Text = "Password" Then
Worksheets("Daten").Visible = True
frmAusEinblenden.cmdDaten.Caption = "Daten ausblenden"
Else
Beep
MsgBox "Falsches Passwort!"
End If
Unload Me
End Sub
ClassModule: frmAusEinblenden
Private Sub cboAusblenden_Change()
Dim wksAct As Worksheet
Dim intCounter As Integer
For Each wksAct In Worksheets
If wksAct.Visible = xlSheetVisible Then
intCounter = intCounter + 1
If intCounter = 2 Then Exit For
End If
Next wksAct
If intCounter = 1 And cboAusblenden.Value <> "" Then
MsgBox "Ein Blatt muss sichtbar bleiben!"
Else
If cboAusblenden.Value <> "" Then
Worksheets(cboAusblenden.Value).Visible = xlHidden
Call UserForm_Initialize
End If
End If
End Sub
Private Sub cboEinblenden_Change()
If cboEinblenden.Value <> "" Then
Worksheets(cboEinblenden.Value).Visible = xlSheetVisible
Call UserForm_Initialize
End If
End Sub
Private Sub cmdDaten_Click()
If cmdDaten.Caption = "Daten einblenden" Then
frmPassword.Show
Else
Worksheets("Daten").Visible = xlVeryHidden
cmdDaten.Caption = "Daten einblenden"
End If
End Sub
Private Sub cmdWeiter_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wksAct As Worksheet
cboEinblenden.Clear
cboAusblenden.Clear
For Each wksAct In ThisWorkbook.Worksheets
Select Case wksAct.Visible
Case xlSheetVisible
If wksAct.Name = "Daten" Then
cmdDaten.Caption = "Daten ausblenden"
Else
cboAusblenden.AddItem wksAct.Name
End If
Case xlSheetHidden
cboEinblenden.AddItem wksAct.Name
Case xlSheetVeryHidden
cmdDaten.Caption = "Daten einblenden"
End Select
Next wksAct
End Sub
StandardModule: basMain
Sub CallForm()
frmAusEinblenden.Show
End Sub