Sub based on form control checkbox

[AIDSNGO]

Hi all,

I am trying to hide a series of rows in a worksheet if a checkbox is not checked. I currently have a module containing:


Sub CheckBox258_Click()
If CheckBox258.Value = False Then
Rows("35:41").EntireRow.Hidden = True
End If
End Sub

Thoughts on what I'm doing wrong?

Thanks so much!

Julien

[CatDaddy]

dont do it on the checkbox click event do it on like a worksheet_change event or something

[AIDSNGO]

Thanks, CatDaddy.

I just tried putting it under worksheet_change, but now I'm getting a runtime error (424 object required). I'm still stumped.

Julien

[CatDaddy]

post your code?

[AIDSNGO]

I didn't really change anything:

Private Sub worksheet_change(ByVal Target As Excel.Range)
...
If CheckBox1.Value = False Then
Rows("35:41").EntireRow.Hidden = True
End If

End Sub

[CatDaddy]

shouldnt it be checkbox258 not checkbox1?

[AIDSNGO]

Yeah, I tried that. Then I tried renaming the checkbox because I thought it was ambiguous with a couple other checkboxes (they were all named 258).

[CatDaddy]

try me.checkbox1.value

[AIDSNGO]

Now it's saying, "Compile error: Method or data member not found".

[snb]

I'd use:



Sub CheckBox258_Click()

Rows("35:41").Hidden = Not checkbox258
End Sub


If you want any value in the worksheet to trigger an event you should use a worksheet_event.
In this case the values in the worksheet do not matter.
Since the value of a checkbox is the cucial thing, it's redundant to let de code check the value of a checkbox every time anything changes in the worksheet.
So I recommend to put your code into the AxtiveX-control event .
Theen there's much less overhead compared to the other suggestion.

[Teeroy]

It depends if it's a checkbox on a userform (the checkbox is a checkbox control) or on a worksheet. And if it's on a worksheet whether it's from the control toolbox (it's an OLE object) or the forms toolbox (it's a shape object).

Assuming it's on the worksheet, and from the forms toolbox, the following should work and from there it's trivial to modify it to suit your application.

Sub test()
Dim sh As Worksheet
Dim s As Shape
Set sh = ActiveSheet
For Each s In Sheet1.Shapes
MsgBox s.Name
MsgBox sh.OLEObjects(s.Name).Object.Value
Next s
End Sub

[snb]

@TR

I don't think so:


Sub snb()
' formcontrols
For Each ct In Sheets(1).CheckBoxes
x0 = ct.Name
x1 = ct.Value
Next

' activeX controls
For Each ct In Sheets(1).OLEObjects
x0 = ct.Name
x1 = ct.Object.Value
Next
End Sub

[Teeroy]

Ooops. You're right SNB I was actually testing on a Checkbox from the control toolbox where it worked.:blush

It turns out that contrary to everything I've come across previously, either type of checkbox on a worksheet is a shape. Something I've never checked for before (pun unintended. Stupid me I believed what I read).

Thanks for the correction and I've learned something new.:bow:

[snb]

You're welcome.
It isn't the most consistent/obvious part of VBA in my opinion.


Sub formcontrols_activeXcontrols_shapes_snb()
' formcontrols
For Each ct In Sheets(1).CheckBoxes
x0 = ct.Name
x1 = ct.Value
Next

' activeX controls
For Each ct In Sheets(1).OLEObjects
x0 = ct.Name
x1 = ct.Object.Value
Next

' shapes
For Each sh In Sheets(1).Shapes
Select Case sh.Type
Case 8 ' formcontrol
x0 = sh.Name
x1 = sh.OLEFormat.Object.Value
Case 12 ' activeX control
x0 = sh.Name
x1 = sh.OLEFormat.Object.Object.Value
End Select
Next
End Sub