My ActiveX Checkboxes won't hide when the row is hidden

I am creating a worksheet for my employer that has many ActiveX checkboxes that when checked unhide rows which contain text boxes or other checkboxes. My frustration has been that the rows that have additional checkboxes are hiding the row but the checkbox is still visible. I have tried multiple solutions including (but not limited to) selecting the "move but don't size" in format controls, grouping the checkboxes and selecting the "move but don't size" and also tried some code that I had found, to no avail.

Note: I am fairly new to VBA codes but have been learning quickly so if there are other things wrong with the codes I have written below or if it somehow is affecting the whole functionality of my sheet let me know. Im VERY grateful for the help!!!

This is the first set of code I used to try and hide the checkboxes:

Sub Hide_other_elec()
     
    Rows("A110:A129:Q110:Q129").Hidden = False
     
     'ActiveSheet.CheckBoxes.Visible = True
     'OR
    Dim objOLE As OLEObject
     
    For Each objOLE In ActiveSheet.OLEObjects
        If objOLE.progID = "Forms.CheckBox.1" Then
            objOLE.Visible = True '/False
        End If
    Next objOLE
     
End Sub

And the second:

Private Sub CheckBox12_Click()
    If CheckBox12.Value Then
        Range("A110:A129:Q110:Q129").EntireRow.Hidden = False
        ActiveSheet.CheckBoxes("Check Box 27").Visible = True
        ActiveSheet.CheckBoxes("Check Box 28").Visible = True
        ActiveSheet.CheckBoxes("Check Box 29").Visible = True
    Else
        Range("A110:A129:Q110:Q129").EntireRow.Hidden = True
        ActiveSheet.CheckBoxes("Check Box 27").Visible = False
        ActiveSheet.CheckBoxes("Check Box 28").Visible = False
        ActiveSheet.CheckBoxes("Check Box 29").Visible = False
    End If
End Sub

Hi Angelammarten and welcome to ExcelForum. Thank you for following the rules by using CODE TAGS. It is much appreciated.

Your first construction is perfectly valid and works on my computer:

Sub ShowOrHideActiveXControls()

 Dim objOLE As OLEObject

 For Each objOLE In ActiveSheet.OLEObjects
        If objOLE.progID = "Forms.Checkbox.1" Then
          Debug.Print objOLE.Name
          objOLE.Visible = True
'          objOLE.Visible = False
        End If
 Next objOLE
 
End Sub

Your second construction for an 'Active X' Checkbox event handler is incorrect. You will need code similar to the following (tested and working):

Sub Checkbox12_Click()
  ActiveSheet.OLEObjects("Checkbox12").Visible = True
End Sub

--------------------

The file associated with post #2 in the following thread may be helpful to you either now or in the future. It creates 'Active X' CheckBoxes and uses one (Class) Event Handler to service all 'Active X' CheckBox change events. http://www.excelforum.com/excel-prog...sition-it.html

-------------------

From your second code sample, you may want to view post #2 on the following thread, which discusses the differences between 'Forms' and 'Active X' controls. http://www.excelforum.com/excel-prog...ml#post3880991


Lewis

Lewis thank you for your help, however it still is not working so perhaps there is more info needed.

The ActiveX checkbox that controls the features is #12

The rows I want hidden are 110-129

The ActiveX checkboxes in those rows are numbered 27-45

There is no linked cell affiliated with Ckbxs 27-45, should there be?

As always thank you a million for your help!!

Ok Thanks Lewis, I've been stumbling over myself for three weeks tweaking this thing There are Active X controls for cells that are associated to a code. I do have form control c box that are more for informational purposes and have no other action tied to it.

Hi Angela,

The ActiveX checkbox that controls the features is #12
The rows I want hidden are 110-129
The ActiveX checkboxes in those rows are numbered 27-45

Try the following code (all checkboxes are 'Active X'):

Private Sub CheckBox12_Click()

 Const sRowsToHIDE = "110:129"
 
  Dim iCheckBoxNumber As Long
  
  Debug.Print "CheckBox12_Click()  CheckBox12.value = " & CheckBox12.Value
  If CheckBox12.Value = True Then
        Range(sRowsToHIDE).EntireRow.Hidden = False
        For iCheckBoxNumber = 27 To 45
          ActiveSheet.OLEObjects("Checkbox" & iCheckBoxNumber).Visible = True
        Next iCheckBoxNumber
  Else
        Range(sRowsToHIDE).EntireRow.Hidden = True
        For iCheckBoxNumber = 27 To 45
          ActiveSheet.OLEObjects("Checkbox" & iCheckBoxNumber).Visible = False
        Next iCheckBoxNumber
  End If
  
End Sub

--------------------------

There is no linked cell affiliated with Ckbxs 27-45, should there be?

There is no need for a 'linked cell' unless you are going to use the value in the 'linked cell' in a workbook formula somewhere.

I hope this helps. Please let me know if you need any further assistance.

Lewis

Success!!! It worked!! Thank you so much Lewis! You are a god among men.

I'm sure I will be back