Office: (Office 2016) VBA: Active Checkbox direkt ansprechen (ohne deren Nummer)

Hallo,

ich habe ein Formula mit ca. 70 Checkboxen erstellt.
Jetzt möchte ich deren Wert mit "CheckBox70.Value = False" zurücksetzen, jedoch ohne manuell 70 Einträge individuell erstellen, bzw. benennen zu müssen.

Ich habe mal ins Blaue versucht, innerhalb der Checkbox-Funktion den Ausdruck "ActiveCheckbox.Value = FALSE" zu verwenden, was aber natürlich nciht geht.

Wie kann ich innerhalb einer Checkbox-Funktion diese aktive Checkbox "unspezifisch" ansprechen?

Und alternativ, falls das nicht geht:
Wie kann ich den Nummernteil des Checkbox-Namens mit einer Variablen ausdrücken, um mit einer Funktion viele Checkboxen ansteuern zu können - abhängig von einer Variablen?

 

Hi,

laufe in einer Schleife über alle CheckBoxen und setze sie auf False. Wenn alle den Namen "Checkbox" haben und durchgehend von 1 bis 70 nummeriert sind dann z.B. so:

Code:

    Dim intBox As Integer
    For intBox = 1 To 70
        Controls("CheckBox" & intBox) = False
    Next intBox

Falls sie nicht durchgehend nummeriert sind müsstest du in einer Case-Anweisung die nicht vorhandenen Nummern ausschließen und die Schleife natürlich bis zu höchsten Nummer laufen lassen.

Andere Möglichkeit: du sprichst sie direkt als Control an:

Code:

    Dim ctrBox As Control
    For Each ctrBox In Controls
        If TypeName(ctrBox) = "CheckBox" Then ctrBox = False
    Next ctrBox

Bis später,
Karin

 

Hallo Karin,

lieben Dank für die Unterstützung!
Variante eins kann ich zumindest im Kern "Controls("CheckBox" & intBox) = False" verwenden, als Schleife brauche ich es aber nicht, da es immer nur eine spezielle Checkbox unter einer besonderen Bedingung betrifft.

Was genau macht die zweite Variante?
Spricht sie nur die benutzte Checkbox an, wenn ich den Code mit deren Funktion aufrufe, oder setzt dieser Code immer alle vorhandenen Checkboxen "am Stück" auf false?

Viele Grüße
Holger

 

Hi Holger,

wo richtig verstehe ich dein Problem nicht - wenn du nur 1 spezifische CheckBox ansprechen willst wenn sie aktiviert/deaktiviert wird, dann gibt es doch das Click-Ereignis dieser CheckBox, denn sie ist in diesem Augenblick ja die aktive.
Erkläre doch mal ganz genau was du eigentlich machen willst.

Bis später,
Karin

 

Es geht grob gesagt um dieses Vorhaben (vereinfachte/veraltete Version des Dokuments im Thread): http://www.office-hilfe.com/support/showthread.php/33794-VBA-Zellen-entsperren-sperren-Problem

Zusammengefasst:
Es sollen elektronische Urlaubsscheine werden, also verschiedene Nutzer (Mitarbeiter und Chef) arbeiten mit dem Dokument und diese sollen jeweils unterschiedliche Checkboxen bedienen. Weiterhin soll z.B. unter eine bestimmten Bedingung eine Checkbox nicht mehr änderbar sein.

Ich kann (soweit ich weiß) erstmal nicht verhindern, das Nutzer A eine Checkbox aktiviert, die er nicht aktivieren soll. Ich kann zwar verhindern, dass die entsprechende Funktion ausgeführt wird, jedoch wird der Status der Checkbox erstmal gesetzt. Und wenn das in ausgewählten Situationen passiert, möchte ich diese eine Checkbox explizit "zurücksetzen", ohne 70 individuelle Anweisungen in die 70 Checkboxen-Funktionen schreiben zu müssen.

Aktuell sieht meine Funktion für jede einzelne Checkbox so aus:

Code:

Private Sub CheckBox2_Click()
z = 5           'die Zeile, in der die Checkbox ist und auf deren Eingaben sie  sich bezieht
CheckMA      'die Funktion mit den Anweisungen, die mit dem Zeilenparameter "z" jeweils ausgeführt werden
End Sub

Wenn ich die Checkbox jetzt unter bestimmten Bedinungen zurücksetzen will, sähe mein derzeitiger Workarround so aus:

Code:

Private Sub CheckBox2_Click()
z = 5
CheckMA

If check = 2 Then               'im Funktionsablauf "CheckMA" wird die Variable "check" vom Wert 1 auf 2 geändert, wenn die Eingabe unberechtigt ist
Auf                                   'die gesperrten Zellen werden mit der Extra-Funktion "Auf" entsperrt 
  Cells(z, 6).ClearContents    ' der Wert der Zelle, in der sich die jeweilige Checkbox befindet, wird gelöscht (WAHR oder FALSCH)
  CheckBox2.Value = False    ' der Zustand der Checkbox selbst wird gelöscht
Zu                                    ' die Funktion "Zu" sperrt das Arbeitsblatt wieder
End If
End Sub

Bei diesem umständlichen Workarround muss ich den If-Block nicht nur 70 mal in die jeweilige Checkbox-Funktion einfügen (was noch ginge), sondern auch 70 mal den Namen der Checkbox anpassen. Und diese Anpassung ist das eigentliche Anliegen der Fragestellung. Also ob Excel nicht mit irgendeiner Syntax von selber weiß, dass es sich bei "Checkbox2" um die Checkbox in der aktiven Funktion "Private Sub CheckBox2_Click()" handelt, ohne das explizit jedes Mal hinschreiben zu müssen.

VG
Holger

 

Hi Holger,

mal ein ganz anderer Ansatz: weshalb sperrst du die CheckBox(en) nicht einfach mittels Enabled = False, die der Benutzer nicht benutzen darf?

Bis später,
Karin

 

Hallo Karin,

danke für den Tipp, das werde ich mal probieren.
Dazu müsste ich sie dann aber auch individuell ansprechen, was aber mit

Code:

Controls("CheckBox" & intBox) = False

gehen sollte?! :)

VG
Holger

 

Hi Holger,

ich meine nicht ihren Wert auf False setzen sondern sie so einzustellen dass sie nicht anklickbar ist - und das geht in der Schleife so:

Code:

Controls("CheckBox" & intBox).Enabled = False

oder eben mit der anderen Variante

Code:

ctrBox.Enabled = False

Bis später,
Karin

 

Hallo Karin,

das habe ich verstanden, ich muss aber die zu sperrenden Checkboxen auch auswählen.
Am Beispiel: Der Mitarbeiter soll seine Reihe an Checkboxen bedienen können, darf aber die vom Chef nicht bedienen.
Und selbst von den Checkboxen des Mitarbeiters müssen die gesperrt werden, welche sich auf bereits bestätigte Einträge beziehen.

Ich kann also mit der Variablen "intBox" die individuell oder per Schleife auswählen, die zu sperren sind?! Das wäre dann meine Lösung, die ich jetzt mal versuche umzusetzen, danke!

Die Variante

Code:

Dim ctrBox As Control
    For Each ctrBox In Controls
        If TypeName(ctrBox) = "CheckBox" Then ctrBox = False
    Next ctrBox

deaktiviert quasi alle Elemente der Kategorie "Controls", verstehe ich das richtig? Und damit immer alle auf einmal?

VG
Holger

 

Vom Prinzip her ja, aber nicht ganz - es ist eine Schleife über alle Steuerelemente, aber es werden nur CeckBoxen deaktiviert durch die If-Abfrage. Wenn du also bestimmte CheckBoxen davon ausschließen willst, dann musst du das in einer weiteren If- oder Select Case-Anweisung tun - z.B. nach diesem Prinzip:

Code:

    Dim ctrBox As Control 'Variable für die Steuerelemente
    For Each ctrBox In Controls 'Schleife über ALLE Steuerelemente
        If TypeName(ctrBox) = "CheckBox" Then 'laufendes Element ist eine ChekBox
            If Environ("UserName") <> "Chef" Then 'Benutzername ist nicht "Chef"
                ctrBox.Locked = True 'laufendes Element sperren
            End If
        End If
    Next ctrBox

Bis später,
Karin

 

Hallo Karin,

dein Lösungsvorschlag hat mich vor eine neue Hürde gestellt ;)

Damit ich direkt die Checkboxen sperren kann, die nicht für den Mitarbeiter da sind, muss ich eine Funktion beim Öffnen des Arbeitsblattes automatisch ausführen. Kurz gegoogelt fand ich als Lösung:

Code:

Option Explicit  '(die vorgeschlagenen Lösungen waren teilweise mit oder ohne dieser Zeile)
Private Sub Workbook_Open()
   (Beispielcode)
End Sub

Leider funktioniert das bei mir nicht. Evtl. liegt es daran, das Code nicht automatisch mit Öffnen der Datei ausgeführt wird, sondern erst eine Sicherheitsabfrage in Excel erscheint, in der man die Inhalte der Makros aktivieren muss?

EDIT 1:
Bleiben die Checkboxen nur für die Sitzung gesperrt, oder müssen sie für den nächsten Benutzer, der die Datei öffnet, entsperrt werden?

EDIT 2:
Zwar funktioniert das automatische Ausführen mit Dateistart noch nicht, aber ich habe es mal testweise per Button versucht, der relevante Codeabschnitt wäre:

Code:

Private Sub Test_Click()
ShowUserName       'Diese Funktion gibt mir in der Variable "MAName" den Benutzernamen wieder

Dim intBox As Integer

If MAName = "Chef" Then
' MA-Checkboxen sperren, Chef-Checkboxen entsperren
    intBox = 1
      For intBox = 1 To 35
        Controls("CheckBox" & intBox) = False
      Next intBox
    
      For intBox = 36 To 70
        Controls("CheckBox" & intBox) = True
      Next intBox

Else
' MA-Checkboxen entsperren, Chef-Checkboxen sperren
    intBox = 1
      For intBox = 1 To 35
        Controls("CheckBox" & intBox) = True
      Next intBox
    
      For intBox = 36 To 70
        Controls("CheckBox" & intBox) = False
      Next intBox
End If
End Sub

Hier markiert es mir beim Ausführen die Zeile "Controls("CheckBox" & intBox) =" mit der Fehlermeldung
"Fehler beim Kompilieren:
Sub oder Function nicht definiert".

 

Hi,

ich hatte bereits geschrieben, dass das Sperren einer Checkbox mittels Befehl "Enabled" realisiert wird. Was du mit deinem Code machen willst ist das Aktivieren der Checkboxen, also das Häkchen setzen.

Folgender gesamter Code gehört ins UserForm und nirgendwo anders hin und er wird bei jedem Start des UserForms erneut ausgeführt:

Code:

Private Sub UserForm_Activate()
     Dim intBox As Integer
     For intBox = 1 To 70
         If Environ("UserName") = "Chef" Then '<==hier kommt der Benutzername des Chefs in "" hin
             If intBox < 36 Then  Controls("CheckBox" & intBox).Enabled = False
         Else
             If intBox > 35 Then Controls("CheckBox" & intBox).Enabled = False
         End If
     Next intBox
End Sub

Bis später,
Karin

 

Hi Karin,

danke, ich denke ich habe das Problem verstanden. Bestimmte Dinge (wie wahrscheinlich auch "Controls"-Anweisungen) funktionieren nur in Userforms und mir war bisher nicht klar, dass es hier überhaupt Unterschiede gibt.

Da ich aber keines habe, sondern mein Formular direkt in der Arbeitsmappe sitzt, muss ich mit den selbstauferlegten Beschränkungen klarkommen.

Danke und VG
Holger

 

Hi Holger,

in deinem Eröffnungsbeitrag hattest du geschrieben, dass du dir ein "Formular" erstellt hast - daraus muss man schlussfolgern, dass es sich um ein UserForm handelt, da du nicht vermerkt hast, dass es sich um ein Tabellenblatt handelt...

Doch auch für Steuerelemente im Tabellenblatt gibt es Möglichkeiten. Allerdings müsste man dazu wissen, um welchen Typ es sich handelt - ActiveX-Steuerelemente oder Formularsteuerelemente. Am einfachsten macht sich so etwas, wenn man eine Beispilemappe hochlädt.

Bis später,
Karin

 

Hi Karin,

mein Fehler, für mich ist es ein "Formular" im Sprachgebrauch, ich wusste nicht, dass der Begriff im Excel eine besondere Form meint.
Ich habe versucht die Datei unter 100 kb zu bekommen, was mir nicht gelang. Also auf diesem Wege.
Das Ganze sieht optisch in der abgespeckten Form erstmal so aus:


Das automatische Freigeben/Sperren der Checkboxen funktioniert nun mit öffnen der Datei, bzw. dem Speichern. Der Code hierfür ist in "DieserArbeitsmappe" wiefolgt:

Code:

Private Sub Workbook_Activate()
' alles sperren
Worksheets("Urlaubsschein").Activate
MAName = "finish"                      'MAName ist die Variable für den Benutzer, der Wert "finish" sorgt in deer Funktion dafür, das alle Zellen gesperrt werden
Call Tabelle1.Checkboxmod           'Diese Funktion findet sich am Ende des Tabellenbereiches

' nutzerspezifische Freigabe
ShowUserName   'Benutzername ermitteln (ist in einem Extra Modul, welches ich hier nicht mit poste da nicht relevant und funktioniert)
Call Tabelle1.Checkboxmod    'zugehörige Checkboxen freigeben/sperren
End Sub

'Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Urlaubsschein").Activate
MAName = "finish"
Call Tabelle1.Checkboxmod
End Sub

Hier jetzt der Code für die Tabelle1(Urlaubsschein). Hierzu muss ich erwänen, dass ich den Code für die Mitarbeiter und den Chef nochmal komplett neu schreiben will und die Version hier so nur genau ein Mal funkioniert, wenn man die Datei geöffnet hat - es gibt also noch einen Fehler, den ich noch nicht gefunden habe. (Der alte, zwar funktionierende Code war viel zu unübersichtlich und verschachtelt geworden).

Code:

Dim z&, s&, p&, i&
Public ifbool As Boolean         'für mehrere If-Anweisungen infolge
Public CheckBoxWert As Boolean   'Status der Checkbox
Public MABoxset As Boolean       'bestimmt abhängig vom User, ob Checkboxen für Mitarbeiter freigegeben oder gesperrt sind
Public ChefBoxset As Boolean     'bestimmt abhängig vom User, ob Checkboxen für Chef freigegeben oder gesperrt sind
'Public Chefname As String  <<<< War dazu gedacht, den Chef-Benutzernamen zentral zu definieren, Abgleich von MAName mit dem String hat aber nicht geklappt
Public tage As Double            'bestätigte Urlaubstage
Public tageplan As Double        'geplante/beantragte Urlaubstage
Public StatusFall As String      'Fallabfrage nach Status

' Mitarbeitereingabe
Private Sub CheckMA()
p = 15  'ist die Spalte, in der die ID des Mitarbeiters abgelegt wird
ifool = False 'testweise um zu verhindern, dass sich die nacheinander laufenden If-Anweisungen beeinflussen
Auf ' entsperren

' es ist noch nichts eingetragen
If Cells(z, 5) = "" And Cells(z, 6) = "" And Cells(z, 9) = "" And ifbool = False Then
  CheckBoxWert = True    'soll den gewünschten Status der Checkbox sicherstellen
  Cells(z, 6) = "WAHR"     'stellt einen Bezug zur Checkbox und der Zelle dar, in der sie liegt (weil ich aktuell nicht auf den jeweiligen CheckboxXXX.Value zugreifen kann
  Cells(z, 5) = "beantragt" 'Status 
  Cells(z, 7) = MAName   'Benutzername
  Cells(z, 8) = Date
  ifbool = True
End If

'MA hat eingetragen und bestätigt, Chef aber noch leer
If Cells(z, 5) = "beantragt" And Cells(z, 6) = "WAHR" And Cells(z, 9) = "" And ifbool = False Then
  CheckBoxWert = False
  Cells(z, 6) = ""
  Cells(z, 5) = ""
  Cells(z, 7) = ""
  Cells(z, 8) = ""
  ifbool = True
End If

'MA hat eingetragen und bestätigt, Chef hat bestätigt
If Cells(z, 5) = "bestätigt" And Cells(z, 6) = "WAHR" And Cells(z, 9) = "WAHR" And ifbool = False Then
  CheckBoxWert = True
  Cells(z, 6) = "WAHR"
  Cells(z, 5) = "bestätigt"
  MsgBox "Ein bestätigter Urlaub muss erst vom Chef zurückgenommen werden."
  ifbool = True
End If

'Chef hat zurückgenommen
If Cells(z, 5) = "(bestät.)" And Cells(z, 6) = "WAHR" And Cells(z, 9) = "FALSCH" And ifbool = False Then
  CheckBoxWert = False
  Cells(z, 6) = "FALSCH"
  Cells(z, 5) = "abgelehnt"
  Cells(z, 7) = MAName
  Cells(z, 8) = Date
  ifbool = True
End If

'Chef hat abgelehnt
If Cells(z, 5) = "abgelehnt" And Cells(z, 6) = "WAHR" And Cells(z, 9) = "FALSCH" And ifbool = False Then
  CheckBoxWert = False
  Cells(z, 6) = "FALSCH"
  Cells(z, 5) = "abgelehnt"
  ifbool = True
End If

arbeitstage
Zu 'sperren
End Sub

' Chef Eingabe
Private Sub CheckChef()
p = 16
ifbool = False
Auf ' entsperren

' MA ha noch nicht bestätigt
If Cells(z, 5) = "" And ifbool = False Then
  CheckBoxWert = False
  Cells(z, 6) = ""
  MsgBox "Der Mitarbeiter muss zuerst bestätigen."
  ifbool = True
End If

' MA hat beantragt, Chef stimmt zu
If Cells(z, 5) = "beantragt" And Cells(z, 9) = "" And ifbool = False Then
  CheckBoxWert = True
  Cells(z, 9) = "WAHR"
  Cells(z, 5) = "bewilligt"
  Cells(z, 10) = "ja, gez. Chef"
  Cells(z, 11) = Date
  Cells(z, 12) = Cells(z, 2)
  Cells(z, 13) = Cells(z, 3)
  ifbool = True
End If

' MA hat beantragt, Chef lehnt ab
If Cells(z, 5) = "beantragt" And Cells(z, 9) = "WAHR" And ifbool = False Then
  CheckBoxWert = False
  Cells(z, 9) = "FALSCH"
  If Cells(z, 11) = Cells(2, 11) Then
   Cells(z, 10) = "abgelehnt," & MAName
   Cells(z, 5) = "abgelehnt"
       Else
   Cells(z, 10) = "zurückgen," & MAName
   Cells(z, 14) = Date
   Cells(z, 5) = "(bestät.)"
  End If
  'Cells(z, 11) = Date
  ifbool = True
End If

arbeitstage
Zu 'sperren
End Sub

Sub arbeitstage()
' ## Regelurlaubstage
tage = 0
tageplan = 0
For i = 1 To 5
  StatusFall = Cells(i + 4, 5).Value
  Select Case StatusFall
              Case "bestätigt"
              tage = tage + Cells(i + 4, 4).Value
              Case "(bestät.)"
              tage = tage + Cells(i + 4, 4).Value
              Case "beantragt"
              tageplan = tageplan + Cells(i + 4, 4).Value
  End Select
Next i
Cells(10, 4).Value = tage + tageplan
Cells(3, 8).Value = Cells(3, 5) - tage - tageplan
Cells(10, 5) = tage
Cells(3, 11) = Cells(3, 5) - tage

End Sub

Sub Auf()
ActiveSheet.Unprotect Password:="1234"
End Sub

Sub Zu()
ActiveSheet.Protect Password:="1234"
End Sub


'########################################################################## Chef
' erste Zeile Chef check
Private Sub CheckBox36_Click()
z = 5
CheckChef
  CheckBox1.Value = CheckBoxWert ' erlaub es dem Wert der Checkbox in der obigen Funktion zu kontrollieren
End Sub

Private Sub CheckBox37_Click()
z = 6
CheckChef
  CheckBox4.Value = CheckBoxWert
End Sub

Private Sub CheckBox38_Click()
z = 7
CheckChef
  CheckBox38.Value = CheckBoxWert
End Sub

Private Sub CheckBox39_Click()
z = 8
CheckChef
 CheckBox39.Value = CheckBoxWert
End Sub

Private Sub CheckBox40_Click()
z = 9
CheckChef
  CheckBox40.Value = CheckBoxWert
End Sub

'########################################################################### MITARBEITER
' Erste Zeile MA check
Private Sub CheckBox1_Click()
z = 5
CheckMA
  CheckBox1.Value = CheckBoxWert
End Sub

' MA check
Private Sub CheckBox2_Click()
z = 6
CheckMA
  CheckBox2.Value = CheckBoxWert
End Sub

' MA check
Private Sub CheckBox3_Click()
z = 7
CheckMA
  CheckBox3.Value = CheckBoxWert
End Sub

' MA check
Private Sub CheckBox4_Click()
z = 8
CheckMA
  CheckBox4.Value = CheckBoxWert
End Sub

' MA check
Private Sub CheckBox5_Click()
z = 9
CheckMA
  CheckBox5.Value = CheckBoxWert
End Sub


' ###################################### Checkboxen sperren
Sub Checkboxmod()

Select Case MAName
     Case "admin"
         MABoxset = True
         ChefBoxset = True
     Case "Chef"
         MABoxset = False
         ChefBoxset = True
     Case "finish"
         MABoxset = False
         ChefBoxset = False
     Case Else
         MABoxset = True
         ChefBoxset = False
End Select

CheckBox1.Enabled = MABoxset
CheckBox2.Enabled = MABoxset
CheckBox3.Enabled = MABoxset
CheckBox4.Enabled = MABoxset
CheckBox5.Enabled = MABoxset

'Chefboxen
CheckBox36.Enabled = ChefBoxset
CheckBox37.Enabled = ChefBoxset
CheckBox38.Enabled = ChefBoxset
CheckBox39.Enabled = ChefBoxset
CheckBox40.Enabled = ChefBoxset
End Sub

In den Einzelnen Checkboxen-Funktionen findet sich jeweils die Zeile

Code:

  CheckBox5.Value = CheckBoxWert

mit dem ich sichergehen will, dass die Checkbox immer den passenden Status hat.
Ich habe diese Zeile testweise auskommentiert, um zu sehen, ob das meinen Fehler vermeidet (das nach öffnen immer nur eine Aktion durchgeführt werden kann), was aber nicht der Fall war.
Dennoch bin ich mir nicht sicher, ob diese Zeile zu einer wiederholung der gesamten Checkbox-Funktion führt?

Das Hauptproblem ist wie gesagt der Wunsch, die Checkboxen direkt zu automatisieren, also solche Wiederholungen wie

Code:

CheckBox1.Enabled = MABoxset
CheckBox2.Enabled = MABoxset
CheckBox3.Enabled = MABoxset
CheckBox4.Enabled = MABoxset
CheckBox5.Enabled = MABoxset

zu vermeiden. Weiterhin könnte ich dann direkt den Wert der Checkbox jeweils aktivierten Checkbox auswerten, anstelle den Umweg über eine Zelle gehen zu müssen.

Viele Grüße und schönes Wochenende,
Holger