Tweeted twitter.com/#!/StackCodeReview/status/589080140212240384

occurred Apr 17, 2015 at 14:57

Reduced indentation

Source Link

edited Apr 17, 2015 at 10:01

144.2k
22
188
473

enter image description here Screenshot

        Private Sub CheckBox21_Change()
        
        With Me.CheckBox21
        If .Value Then ClearGroup .GroupName, .Name
        End With
        
        End Sub
        
        Private Sub CheckBox22_Change()
        
        With Me.CheckBox22
        If .Value Then ClearGroup .GroupName, .Name
        End With
        
        End Sub
        
        Private Sub ClearGroup(sGroup As String, sName As String)
            Dim ole As OLEObject
            
                For Each ole In Me.OLEObjects
                    If TypeName(ole.Object) = "CheckBox" Then
                        If ole.Object.GroupName = sGroup And ole.Name <> sName Then
                            ole.Object.Value = False
                        End If
                    End If
                Next ole
        End Sub
        
        Private Sub CheckBox21_Click()
            If CheckBox21.Value = True Then
            Range("i6", "m6").Value = "High"
            Range("r3").Value = "H"
            Range("i6", "m6").Interior.Color = RGB(217, 0, 0)
            Range("r3").Interior.Color = RGB(217, 0, 0)
            
            Else
            CheckBox21.Value = False
            Range("i6", "m6").Value = "Low"
            Range("r3").Value = "L"
            Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
            Range("r3").Interior.Color = RGB(153, 204, 0)
            End If
        
        End Sub
        Private Sub CheckBox22_Click()
            If CheckBox22.Value = True Then
            Range("i6", "m6").Value = "Medium"
            Range("r3").Value = "M"
            Range("i6", "m6").Interior.Color = RGB(255, 204, 0)
            Range("r3").Interior.Color = RGB(255, 204, 0)
            
            Else
            CheckBox22.Value = False
            Range("i6", "m6").Value = "Low"
            Range("r3").Value = "L"
            Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
            Range("r3").Interior.Color = RGB(153, 204, 0)
            End If
        End Sub

Is there any other way I can write the VBA code such that the overall line of code will be reduced?

Thank you.

enter image description here

        Private Sub CheckBox21_Change()
        
        With Me.CheckBox21
        If .Value Then ClearGroup .GroupName, .Name
        End With
        
        End Sub
        
        Private Sub CheckBox22_Change()
        
        With Me.CheckBox22
        If .Value Then ClearGroup .GroupName, .Name
        End With
        
        End Sub
        
        Private Sub ClearGroup(sGroup As String, sName As String)
            Dim ole As OLEObject
            
                For Each ole In Me.OLEObjects
                    If TypeName(ole.Object) = "CheckBox" Then
                        If ole.Object.GroupName = sGroup And ole.Name <> sName Then
                            ole.Object.Value = False
                        End If
                    End If
                Next ole
        End Sub
        
        Private Sub CheckBox21_Click()
            If CheckBox21.Value = True Then
            Range("i6", "m6").Value = "High"
            Range("r3").Value = "H"
            Range("i6", "m6").Interior.Color = RGB(217, 0, 0)
            Range("r3").Interior.Color = RGB(217, 0, 0)
            
            Else
            CheckBox21.Value = False
            Range("i6", "m6").Value = "Low"
            Range("r3").Value = "L"
            Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
            Range("r3").Interior.Color = RGB(153, 204, 0)
            End If
        
        End Sub
        Private Sub CheckBox22_Click()
            If CheckBox22.Value = True Then
            Range("i6", "m6").Value = "Medium"
            Range("r3").Value = "M"
            Range("i6", "m6").Interior.Color = RGB(255, 204, 0)
            Range("r3").Interior.Color = RGB(255, 204, 0)
            
            Else
            CheckBox22.Value = False
            Range("i6", "m6").Value = "Low"
            Range("r3").Value = "L"
            Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
            Range("r3").Interior.Color = RGB(153, 204, 0)
            End If
        End Sub

Is there any other way I can write the VBA code such that the overall line of code will be reduced?

Thank you.

Screenshot

Private Sub CheckBox21_Change()

With Me.CheckBox21
If .Value Then ClearGroup .GroupName, .Name
End With

End Sub

Private Sub CheckBox22_Change()

With Me.CheckBox22
If .Value Then ClearGroup .GroupName, .Name
End With

End Sub

Private Sub ClearGroup(sGroup As String, sName As String)
    Dim ole As OLEObject
    
        For Each ole In Me.OLEObjects
            If TypeName(ole.Object) = "CheckBox" Then
                If ole.Object.GroupName = sGroup And ole.Name <> sName Then
                    ole.Object.Value = False
                End If
            End If
        Next ole
End Sub

Private Sub CheckBox21_Click()
    If CheckBox21.Value = True Then
    Range("i6", "m6").Value = "High"
    Range("r3").Value = "H"
    Range("i6", "m6").Interior.Color = RGB(217, 0, 0)
    Range("r3").Interior.Color = RGB(217, 0, 0)
    
    Else
    CheckBox21.Value = False
    Range("i6", "m6").Value = "Low"
    Range("r3").Value = "L"
    Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
    Range("r3").Interior.Color = RGB(153, 204, 0)
    End If

End Sub
Private Sub CheckBox22_Click()
    If CheckBox22.Value = True Then
    Range("i6", "m6").Value = "Medium"
    Range("r3").Value = "M"
    Range("i6", "m6").Interior.Color = RGB(255, 204, 0)
    Range("r3").Interior.Color = RGB(255, 204, 0)
    
    Else
    CheckBox22.Value = False
    Range("i6", "m6").Value = "Low"
    Range("r3").Value = "L"
    Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
    Range("r3").Interior.Color = RGB(153, 204, 0)
    End If
End Sub

Is there any other way I can write the VBA code such that the overall line of code will be reduced?

Source Link

asked Apr 17, 2015 at 9:51

Guzzyman

153
1
1
4

Working with group of checkboxes in MS Excel

I have the following checkbox as depicted by the image below:

enter image description here

The checkboxes are in 3 groups. Confidentiality, Integrity ad Availability. When the first in is clicked, the Confidentiality turns Red for High, Orange for Medium and Green for Low.

This is a lot of work to be done as you need to create a subroutine for each checkbox control. Below is a sample code for 2 checkboxes in the first line in the image above.

        Private Sub CheckBox21_Change()
        
        With Me.CheckBox21
        If .Value Then ClearGroup .GroupName, .Name
        End With
        
        End Sub
        
        Private Sub CheckBox22_Change()
        
        With Me.CheckBox22
        If .Value Then ClearGroup .GroupName, .Name
        End With
        
        End Sub
        
        Private Sub ClearGroup(sGroup As String, sName As String)
            Dim ole As OLEObject
            
                For Each ole In Me.OLEObjects
                    If TypeName(ole.Object) = "CheckBox" Then
                        If ole.Object.GroupName = sGroup And ole.Name <> sName Then
                            ole.Object.Value = False
                        End If
                    End If
                Next ole
        End Sub
        
        Private Sub CheckBox21_Click()
            If CheckBox21.Value = True Then
            Range("i6", "m6").Value = "High"
            Range("r3").Value = "H"
            Range("i6", "m6").Interior.Color = RGB(217, 0, 0)
            Range("r3").Interior.Color = RGB(217, 0, 0)
            
            Else
            CheckBox21.Value = False
            Range("i6", "m6").Value = "Low"
            Range("r3").Value = "L"
            Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
            Range("r3").Interior.Color = RGB(153, 204, 0)
            End If
        
        End Sub
        Private Sub CheckBox22_Click()
            If CheckBox22.Value = True Then
            Range("i6", "m6").Value = "Medium"
            Range("r3").Value = "M"
            Range("i6", "m6").Interior.Color = RGB(255, 204, 0)
            Range("r3").Interior.Color = RGB(255, 204, 0)
            
            Else
            CheckBox22.Value = False
            Range("i6", "m6").Value = "Low"
            Range("r3").Value = "L"
            Range("i6", "m6").Interior.Color = RGB(153, 204, 0)
            Range("r3").Interior.Color = RGB(153, 204, 0)
            End If
        End Sub

Is there any other way I can write the VBA code such that the overall line of code will be reduced?

Thank you.

excel

Stack Exchange Network

Return to Question

Working with group of checkboxes in MS Excel