[resolved] VBA script to hide/unhide rows not unhiding... but no errors

[jotekman]

So, I have a sheet made with checkboxes that is pretty interconnected. I am adding a function to a previous macro so I also have a checked box with rows visible, and when unchecked, they are not visible.
my code is thus:

Sub ClearCheckBoxesRange1()
    Dim chk As CheckBox

    For Each chk In ActiveSheet.CheckBoxes
      If Not Intersect(chk.TopLeftCell, Range("C4:C11")) Is Nothing Then
        chk.Value = ActiveSheet.CheckBoxes("Check Box 240").Value
      End If
        Next chk
    
      If Checkbox240 = False Then
        Sheet7.Range("A40:A41").EntireRow.Hidden = True
      ElseIf Checkbox240 = True Then
        Sheet7.Range("A40:A41").EntireRow.Hidden = False
      End If

End Sub

Everything works, including the first function (checking or unchecking a range of other checkboxes) and actually hiding the rows 40 and 41 work. But when checking the 240 box again, nothing happens. But no errors come out. Anyone know what I am missing?
Thanks for you time!

[jotekman]

I also tried instead:

Worksheets("Investment Summary Coding Tests").Rows(40).EntireRow.Hidden = Not (CheckBox240.Value)

But I can't even get it to run....

[Zvoni]

Don't use ActiveSomething

And in your first part you're looking for a Checkbox called "Check Box 240" (note the spaces between words)

btw: Your second half can be cut down to a single line

Sheet7.Range("A40:A41").EntireRow.Hidden = Not Checkbox240.Value

[jotekman]

Don't use ActiveSomething

And in your first part you're looking for a Checkbox called "Check Box 240" (note the spaces between words)

btw: Your second half can be cut down to a single line

Sheet7.Range("A40:A41").EntireRow.Hidden = Not Checkbox240.Value

Thank you for the answer!
So, I changed the active to the sheet reference, and inserted the code you sent, as so:

Sub ClearCheckBoxesRange1()
    Dim chk As CheckBox

    For Each chk In Sheet3.CheckBoxes
      If Not Intersect(chk.TopLeftCell, Range("C4:C11")) Is Nothing Then
        chk.Value = Sheet3.CheckBoxes("Check Box 240").Value
      End If
        Next chk

Sheet7.Range("A40:A41").EntireRow.Hidden = Not Checkbox240.Value

End Sub

I tried similar before, and I get the same error "Run-time error '424', object required".
I was under the impression I didn't need an object in that expression, but apparently I am mistaken Again, any ideas?
And, sorry, I probably should have mentioned that it was on another sheet, sheet 7, that I needed the hiding to happen. The first half still works well, though, just it errors at the hiding line...

[Zvoni]

There is no Checkbox with the name „Check Box 240“

[jotekman]

There actually is a Checkbox 240, but on Sheet 3.

Sheet7.Range("A40:A41").EntireRow.Hidden = Not Checkbox240.Value

I would guess that since this line is referencing Sheet7,it is looking for checkbox240 on sheet 7. Is there a way to reference sheet3 checkbox 240 in this line?

[Zvoni]

I meant your Line 6 in your code

and you can fully qualify each control: sheet3.checkbox240.value

[westconn1]

Sub ClearCheckBoxesRange1()
Dim chk As CheckBox

For Each chk In Sheet3.CheckBoxes
If Not Intersect(chk.TopLeftCell, Range("C4:C11")) Is Nothing Then
chk.Value = Sheet3.CheckBoxes("Check Box 240").Value
End If
Next chk

Sheet7.Range("A40:A41").EntireRow.Hidden = Not Checkbox240.Value

End Sub

i am not sure why you are iterating all the checkboxes as you do nothing with any result from the loop and the chk object goes out of scope at the end of the procedure
also you do not specify which module or code pane your code is running in

on a little testing i did (excel 2010) with form controls the checked state of returned in the value of the checkbox does not return true or false, but 1 and -4146, so while the 1 may convert to true, the other will not ever be false
you can work around the problem like

Debug.Print CBool(Sheet1.CheckBoxes("check box 1").Value + 4146)

this has returned the correct true or false, you should easily be able to adapt this into your code
activex controls and user forms are different and will return true or false, but if the code is not in the code pane for the sheet containing the checkbox, you will have to address it through the shapes collection of the sheet
so you need to determine the type of checkbox control you are using, judging by the name, looks like a forms control, then decide which will be best for your application

[jotekman]

Thanks for the answer! The idea for the loop was to run through all the checkboxes in that range, and match them to the value of Check Box 240, so if it is checked, they would become checked, and if unchecked, they uncheck automatically. And that part seems to work well, but if there is a better and more correct way to do it, I would love to know it!

As for the 2nd part, thank you!
I actually, late last night, from bits and pieces that different people suggested, ended up with this:

Sub ClearCheckBoxesRange1()
    Dim chk As CheckBox
    Dim var As String
    var = Range("X4").Value
    For Each chk In Sheet3.CheckBoxes
      If Not Intersect(chk.TopLeftCell, Range("C4:C11")) Is Nothing Then
        chk.Value = Sheet3.CheckBoxes("Check Box 240").Value
      End If
        Next chk

If Worksheets("Category Input").Range("X4").Value = True Then
    Sheet5.Range("A40:A41").EntireRow.Hidden = False
Else
    Sheet5.Range("A40:A41").EntireRow.Hidden = True
End If

End Sub

I ended up figuring it would be best to just link a cell and use the true false from the linked cell, rather than the method you suggested, though in the future, that is awesome, and I appreciate it! I will go back and integrate.
Thanks!