How can I hide check box created via FORMS together with column?

[Guest]

Hey guys, how can I hide a check box created using FORMS together with column
it is placed in (or in some other way). "Move and size with cell" option is
not active in object positioning for check boxes created via FORMS as opposed
to those created via CONTROL TOOLBOX.

Thanks,
Max

 

[Dave Peterson]

I think you'll have better luck (pronounced easier time!) with the checkboxes
from the Control toolbox toolbar.

But maybe you could have a macro that hides the columns/rows and also looks at
the objects to see if they should be .visible = false.

And the same kind of macro to make the checkbox's visibility true.

 

[Guest]

Thanks Dave!

ActiveSheet.Shapes("Check Box 1").Select
Selection.Visible = True

This thing hid the checkbox but alternative one

ActiveSheet.Shapes("Check Box 1").Select
Selection.Visible = False

does not help with making it visible again.

Actually what I need is to hide unchecked boxes by unpressing toggle button
and show all of them again by pressing it. I've done it for rows, but all
these checkboxes overlapping each other look ugly.

Else 'the button is unpressed

For rwindex = 20 To 217
For colIndex = 10 To 10
With Worksheets("KPI").Cells(rwindex, colIndex)
If .Value = False Then Worksheets("KPI").Rows(rwindex).Select
Selection.EntireRow.Hidden = True
range(cells(rwindex,10),cells(rwindex,10).
End With
Next colIndex
Next rwindex

I will really appreciate if you can help me to upgrade this stuff to enable
hiding/unhiding checkboxes.

Thanks!

 

[Dave Peterson]

So you're making a routine that will look at each checkbox and hide/show the row
based on the checkbox that's located in a cell in that row?

I'm not quite sure how you're going to show that row--since the checkbox will be
hidden and unavailable for changing???

But maybe something like this to hide the rows:
Option Explicit
Sub HideRows()
Dim myCBX As CheckBox
For Each myCBX In ActiveSheet.CheckBoxes
If myCBX.Value = xlOn Then
myCBX.Visible = False
myCBX.TopLeftCell.EntireRow.Hidden = True
End If
Next myCBX
End Sub

And something like this to show the rows, unhide the checkboxes and clear them:

Sub ShowAllRows()
With ActiveSheet
.Rows.Hidden = False
With .CheckBoxes
.Visible = True
.Value = xlOff
End With
End With
End Sub