none
VBA CheckBox Controls RRS feed

  • Question

  • I have 200+ Check box options in my word document and each one is supposed to be highlighted when checked so I have 200+ private subs similar to the following

    Private Sub CheckBox5_Click()
       If CheckBox5.Value = True Then
          CheckBox5.BackColor = vbGreen
       Else:
          CheckBox5.BackColor = vbWhite
       End If
    End Sub

    I'm trying to figure out a way to write one macro to make all 200+ checkboxes change their background color to green when checked. Can anyone point me in the right direction?

    Friday, September 4, 2015 5:43 PM

Answers

  • Hi MitCol,

    To add same events to multiple controls, we can write a custom class module. And initialize the class instance based on the controls in the document. Here is an example for your reference:
    Class module(CBSEvent):

    Public WithEvents cb As MSForms.CheckBox
    
    Private Sub cb_Click()
    If cb.Value = True Then
        cb.BackColor = vbGreen
    Else
        cb.BackColor = vbWhite
    End If
    End Sub
    
    ThisDocument module:
    Dim checkboxs() As CBSEvent
    
    
    Private Sub Document_Open()
    Dim checkboxNum As Integer
    checkboxNum = 0
      For Each obj In ActiveDocument.InlineShapes
            If obj.OLEFormat.ClassType = "Forms.CheckBox.1" Then
                  checkboxNum = checkboxNum + 1
               
            End If
        Next obj
    ReDim checkboxs(checkboxNum)
    
    checkboxNum = 0
    For Each obj In ActiveDocument.InlineShapes
            If obj.OLEFormat.ClassType = "Forms.CheckBox.1" Then
                    
                Set checkboxs(checkboxNum) = New CBSEvent
                Set checkboxs(checkboxNum).cb = obj.OLEFormat.Object
                checkboxNum = checkboxNum + 1
            End If
        Next obj
        
        
    End Sub
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 7, 2015 7:35 AM
    Moderator

All replies

  • I have 200+ Check box options in my word document and each one is supposed to be highlighted when checked so I have 200+ private subs similar to the following

    Private Sub CheckBox5_Click()
       If CheckBox5.Value = True Then
          CheckBox5.BackColor = vbGreen
       Else:
          CheckBox5.BackColor = vbWhite
       End If
    End Sub

    I'm trying to figure out a way to write one macro to make all 200+ checkboxes change their background color to green when checked. Can anyone point me in the right direction?

    • Merged by Youjun Tang Monday, September 7, 2015 3:31 AM duplicate
    Friday, September 4, 2015 3:54 PM
  • This is the windows forms forum, rather than office development.
    You'd do better asking in a more appropriate forum.

    .

    I noticed this article though:

    http://office-2all.blogspot.co.uk/2011/09/colour-form-field-check-box-with.html

    That does fore colour rather than the background, you'd have to find the right property to set.

    Assuming it's possible.


    Friday, September 4, 2015 4:50 PM
  • Hi MitCol,

    To add same events to multiple controls, we can write a custom class module. And initialize the class instance based on the controls in the document. Here is an example for your reference:
    Class module(CBSEvent):

    Public WithEvents cb As MSForms.CheckBox
    
    Private Sub cb_Click()
    If cb.Value = True Then
        cb.BackColor = vbGreen
    Else
        cb.BackColor = vbWhite
    End If
    End Sub
    
    ThisDocument module:
    Dim checkboxs() As CBSEvent
    
    
    Private Sub Document_Open()
    Dim checkboxNum As Integer
    checkboxNum = 0
      For Each obj In ActiveDocument.InlineShapes
            If obj.OLEFormat.ClassType = "Forms.CheckBox.1" Then
                  checkboxNum = checkboxNum + 1
               
            End If
        Next obj
    ReDim checkboxs(checkboxNum)
    
    checkboxNum = 0
    For Each obj In ActiveDocument.InlineShapes
            If obj.OLEFormat.ClassType = "Forms.CheckBox.1" Then
                    
                Set checkboxs(checkboxNum) = New CBSEvent
                Set checkboxs(checkboxNum).cb = obj.OLEFormat.Object
                checkboxNum = checkboxNum + 1
            End If
        Next obj
        
        
    End Sub
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 7, 2015 7:35 AM
    Moderator