Rows to drop off (hide) when a value is entered into a cell

[Guest]

I am trying to learn how to get a row or rows to auto hide when a value is
entered into a specific cell within that row. Any ideas?

Thanks!
Jason Beckman

 

[Guest]

I have just seen a similar Macro for hiding rows...
Can someone help me create a 'CheckBox" Macro that when checked, it hides
the row the checkbox was in?

Much thanks!

 

[Dave Peterson]

I used a checkbox from the control toolbox toolbar--they work better when the
row is hidden.

This is the code behind the checkbox:

Option Explicit
Private Sub CheckBox1_Click()

With Me.CheckBox1
.TopLeftCell.EntireRow.Hidden = .Value
End With

End Sub


I also rightclicked on the checkbox, chose format Control|Properties tab
check "Move and Size with cells"

(Once the checkbox is checked the checkbox is hidden, right???)

 

[Guest]

Mr. Peterson,
Thank you for the help! I really appreciate it.
Can I replace CheckBox1 with CheckBox(Wildcard)? So that all checkboxes in
all rows act the same as the way the Macro does that you provided me with?

Thanks again.

Jason

 

[Dave Peterson]

Nope.

Each of those checkboxes from the control toolbox need their own _click event.

But you use something like this to use just one procedure that does all the real
work.

All this stuff goes behind the worksheet with the checkboxes:

Option Explicit
Private Sub CheckBox1_Click()
Call DoTheWork(1)
End Sub
Private Sub CheckBox2_Click()
Call DoTheWork(2)
End Sub
Private Sub CheckBox3_Click()
Call DoTheWork(3)
End Sub
Sub DoTheWork(myIndex As Long)
Dim OLEObj As OLEObject
Set OLEObj = Me.OLEObjects("Checkbox" & myIndex)
With OLEObj
.TopLeftCell.EntireRow.Hidden = .Object.Value
End With
End Sub


=======
One of the nice things about the checkboxes from the Forms toolbar (not the
control toolbox toolbar) is that you can assign the same macro to all of them.

One of the bad things about those Forms toolbar checkboxes is that they don't
play nice with hidden rows. If you look, you'll see that "move and size with
cells" isn't even an option.