Possible To Hide Rows That Contain Checkboxes?

[Bingo969]

Good Morning -

I'm using Excel 2003 and Windows XP (Though also testing Excel 2010 on Windows 7)

I have a workbook that contains multiple checkboxes.

I want to have multiple sections of my worksheet hidden and only unhide relevant sections of the workbook when the user selects that particular checkbox.

The problem I'm running into some of those sections I want to have hidden (and therefore unhide on a click-event) ALSO have checkboxes.

Of course, when rows are hidden all the checkboxes still stay on top since they are just objects.

Is there a way not only to hide those (I'd guess you somehow need to 'bind' them to a cell?) but then also have them unhidden as part of a checkbox macro?

Hope this makes sense and thank you!

Bingo

 

[Andrew Poulsom]

Have you tried checking 'Move and size with cells' on the Properties tab of the Format Control dialog?

 

[Bingo969]

I wonder if it only really works when you use the ActiveX type checkboxes. I'm using the more basic forms toolbar rather than the controls tool.

With these you can only choose 'Move but don't size' rather than move and size.

Thanks for the tip though, it spurred me to think down that road which is why I suspect I've figured out my own answer which is that it's not possible unless I re-do all the checkboxes.

Thank you!

B

 

[Patrick_]

I wonder if it only really works when you use the ActiveX type checkboxes. I'm using the more basic forms toolbar rather than the controls tool.

With these you can only choose 'Move but don't size' rather than move and size.

Thanks for the tip though, it spurred me to think down that road which is why I suspect I've figured out my own answer which is that it's not possible unless I re-do all the checkboxes.

Thank you!

B

You first need to select all the checkboxes and group them together. this is in the page layout section then "arrange" and then "group". once they are grouped together you can select "move and size with cells" and they will hide just fine.

 

[p_kermpf]

I tried to follow the instructions above. I grouped them and did then tried to hide/unhide. I placed form checkboxes in column b and made column b very wide, wide enough to be wider than the checkboxes. ( BTW: you need two or more checkboxes to group them. ) I clicked hide column b and column b hid, but the checkbox moved to column c. I hid column c and the boxes hid. Now, I unhid columns b and c. Then tried hiding c only. C hides but boxes remain. It appears even though I have checkboxes in column b only, I have to hide both b and c to hide the form checkboxes. Odd. As I was trying to hide the boxes, part of your instruction calls for arranging. So I created this little macro to see if it would hide/unhide the checkboxes. Here is the macro:
Sub arrangecells()
'
' arrangecells Macro
' remove checkboxes
'
' Keyboard Shortcut: Ctrl+a
'
ActiveSheet.Shapes.Range(Array("Check Box 2")).Visible = msoFalse
ActiveSheet.Shapes.Range(Array("Check Box 1")).Visible = msoFalse
For i = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Visible = msoTrue
Next i
End Sub

Pressing Ctrl-a did nothing. What's the best fix?

 

[markddelorme]

I have a very similar problem. I created a file in Excel 2013, which works perfectly at hiding rows that contain checkboxes and ALSO at unhiding these rows with the checkboxes visible. When I transferred my workbook from my personal computer to my work computer, which runs Excel 2010, the hide/unhide rows functions no longer behave accordingly.

I've tried everything in terms of changing the properties to "Move and size with cells", and the checkboxes are hiding properly with the rows, but it's the unhiding procedure that fails to unhide the checkboxes. The rows unhide correctly, but the checkboxes within each of these rows all become superimposed at the very top row, and they aren't even visible! Hence, I need to individually locate each checkbox object and delete them.

What's even more strange is that the function actually works perfectly when I originally set up the worksheet and try it a few times, but it's only after I save my file and exit the program that once I re-open my same file, it's behaving this way. Any help would be greatly appreciated!

 

[mikerickson]

Perhaps this will help you out.

Make everything visible and set the shapes where you want them. Then run SetUp, which will put the cell address in each shape (checkbox) AlternativeText property. (Since AlternativeText applies only to web use and only in a limited fashion, its a useful place to attach data to a shape.)

After that is done, at any time, running the Sub RestoreAllShapes will put the shapes back over (and the same size as) the cell where they were originally located.
There should probably be some error checking added to RestoreAllShapes and the .Height and .Width lines might need to be omitted, but the consept might be useful.

Sub SetUp()
    Dim oneShape As Shape
    For Each oneShape In ActiveSheet.Shapes
        oneShape.AlternativeText = oneShape.TopLeftCell.Address
    Next oneShape
End Sub

Sub RestoreAll()
    Dim oneShape As Shape
    
    For Each oneShape In ActiveSheet.Shapes
        With ActiveSheet.Range(oneShape.AlternativeText)
            If Not .EntireRow.Hidden Then
                oneShape.Top = .Top
                oneShape.Left = .Left
                oneShape.Height = .Height
                oneShape.Width = .Width
            End If
        End With
    Next oneShape
End Sub

 

[markddelorme]

Thanks Mike. That is a good workaround, although it doesn't solve my problem because there is a Worksheet_Activate sub in place that autofilters and hide most of the rows based on data from on a preceding sheet, a type of user form auto-completion. I actually found another suitable work-around that involves creating a "check box" from drawing shapes and a relatively simple macro.