ActiveX steuert Formular CheckBoxen...

Frage:

Mit einem ActiveX Steuerelement werden Formular Checkboxen gesteuert. Einmal alle Checkboxen in drei Spalten. Dann die CheckBoxen in jeder Spalte für sich. Wie geht das?

With an ActiveX control form checkboxes are controlled. Once all of the checkboxes in three columns. Then the check boxes in each column of its own. How does it work?

Hier noch eine Beispieldatei / Here's a sample file:
ActiveX steuert Formular CheckBoxen...[XLS 90 KB]

Option Explicit
'-------------------------------------------------------------------------- 
' Module    : Sheet1 
' Procedure : CheckBox1_Click 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 07.01.2013 
' Purpose   : ActiveX controls form checkboxes... 
'-------------------------------------------------------------------------- 
Private Sub CheckBox1_Click()
    With CheckBox1
        If .Value Then
            Call Check_1(.TopLeftCell.Column, .TopLeftCell.Row, 1)
        Else
            Call Check_1(.TopLeftCell.Column, .TopLeftCell.Row, 0)
        End If
    End With
End Sub

Option Explicit
'-------------------------------------------------------------------------- 
' Module    : Sheet2 
' Procedure : CheckBox1_Click 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 07.01.2013 
' Purpose   : ActiveX controls form checkboxes... 
'-------------------------------------------------------------------------- 
Private Sub CheckBox1_Click()
    With CheckBox1
        If .Value Then
            Call Check_2(.TopLeftCell.Column, .TopLeftCell.Row, 1)
        Else
            Call Check_2(.TopLeftCell.Column, .TopLeftCell.Row, 0)
        End If
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Sheet2 
' Procedure : CheckBox2_Click 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 07.01.2013 
' Purpose   : ActiveX controls form checkboxes... 
'-------------------------------------------------------------------------- 
Private Sub CheckBox2_Click()
    With CheckBox2
        If .Value Then
            Call Check_2(.TopLeftCell.Column, .TopLeftCell.Row, 1)
        Else
            Call Check_2(.TopLeftCell.Column, .TopLeftCell.Row, 0)
        End If
    End With
End Sub
'-------------------------------------------------------------------------- 
' Module    : Sheet2 
' Procedure : CheckBox3_Click 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 07.01.2013 
' Purpose   : ActiveX controls form checkboxes... 
'-------------------------------------------------------------------------- 
Private Sub CheckBox3_Click()
    With CheckBox3
        If .Value Then
            Call Check_2(.TopLeftCell.Column, .TopLeftCell.Row, 1)
        Else
            Call Check_2(.TopLeftCell.Column, .TopLeftCell.Row, 0)
        End If
    End With
End Sub

Option Explicit
'-------------------------------------------------------------------------- 
' Module    : Module1 
' Procedure : Check_1 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 07.01.2013 
' Purpose   : ActiveX controls form checkboxes... 
'-------------------------------------------------------------------------- 
Public Sub Check_1(ByVal lngColumn As Long, _
    ByVal lngRow As Long, ByVal intOnOff As Integer)
    Dim strTMP As String
    Dim shpBox As Shape
    On Error GoTo Fin
    With Sheet1
        For Each shpBox In .Shapes
            With shpBox
                If .TopLeftCell.Row > lngRow Then
                    If .TopLeftCell.Column = lngColumn Then
                        If .FormControlType = xlCheckBox Then
                            .ControlFormat.Value = intOnOff
                        End If
                    ElseIf .TopLeftCell.Column = lngColumn + 1 Then
                        If .FormControlType = xlCheckBox Then
                            .ControlFormat.Value = intOnOff
                        End If
                    ElseIf .TopLeftCell.Column = lngColumn + 2 Then
                        If .FormControlType = xlCheckBox Then
                            .ControlFormat.Value = intOnOff
                        End If
                    End If
                End If
            End With
        Next shpBox
    End With
Fin:
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & " " & Err.Description
End Sub

Option Explicit
'-------------------------------------------------------------------------- 
' Module    : Module2 
' Procedure : Check_2 
' Author    : Case (Ralf Stolzenburg) 
' Date      : 07.01.2013 
' Purpose   : ActiveX controls form checkboxes... 
'-------------------------------------------------------------------------- 
Public Sub Check_2(ByVal lngColumn As Long, _
    ByVal lngRow As Long, ByVal intOnOff As Integer)
    Dim strTMP As String
    Dim shpBox As Shape
    On Error GoTo Fin
    With Sheet2
        For Each shpBox In .Shapes
            With shpBox
                If .TopLeftCell.Row > lngRow Then
                    If .TopLeftCell.Column = lngColumn Then
                        If .FormControlType = xlCheckBox Then
                            .ControlFormat.Value = intOnOff
                        End If
                    End If
                End If
            End With
        Next shpBox
    End With
Fin:
    If Err.Number <> 0 Then MsgBox "Fehler: " & _
        Err.Number & " " & Err.Description
End Sub

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...