Excel 2013 VBA Moving ActiveX checkboxes 2

[DSaba10]

I'm having some issues with some VBA code. I've got a checkbox list and a button at the bottom that hides all of the rows/checkboxes that are not selected. The problem is that when I hit the box to view all again, the checkboxes are then all piled on top of each other rather than where they were. Here's the code I have to hide/view the rows and checkboxes:

Sub HURows()
    EndRow = 15
    Set ws = ThisWorkbook.Worksheets("Project Parameters")
    
    Range("B11").Select
        
    With ws
    For RowCnt = 1 To EndRow
        If ActiveCell.Value = False Then
            ActiveCell.EntireRow.Hidden = True
            For Each chkBox In ws.OLEObjects
                If TypeName(chkBox.Object) = "Checkbox" & RowCnt Then
                    chkBox.Visible = False
                End If
            Next chkBox
        Else
            ActiveCell.EntireRow.Hidden = False
            For Each chkBox In ws.OLEObjects
                If TypeName(chkBox.Object) = "Checkbox" & RowCnt Then
                    chkBox.Visible = True
                End If
            Next chkBox
        End If
        ActiveCell.Offset(1, 0).Select
    Next
    End With
End Sub

And...

Sub HUNRows()
    EndRow = 15
    Set ws = ThisWorkbook.Worksheets("Project Parameters")

    Range("B11").Select
    
    With ws
    For RowCnt = 1 To EndRow
        If ActiveCell.Value = True Then
            ActiveCell.EntireRow.Hidden = False
            For Each chkBox In ws.OLEObjects
                If TypeName(chkBox.Object) = "Checkbox" & RowCnt Then
                    chkBox.Visible = True
                End If
            Next chkBox
        Else
            ActiveCell.EntireRow.Hidden = False
            For Each chkBox In ws.OLEObjects
                If TypeName(chkBox.Object) = "Checkbox" & RowCnt Then
                    chkBox.Visible = False
                End If
            Next chkBox
        End If
        ActiveCell.Offset(1, 0).Select
    Next
    End With
End Sub

Is there any way to keep the checkboxes locked in place when hiding and making visible again?

Thanks

 

[DSaba10]

I should clarify... I want the checkboxes to remain locked to their row. So if rows 11 and 12 have a selected box, rows 13-16 are unselected, and row 17 is checked, it will hide the appropriate rows the checkboxes will stay aligned with their row when hidden and when not.

 

[DjangMan]

Maybe have a look at the .top property before and after hiding/unhiding. You might have to store that in the .tag property and re-assign it to .top when showing the checkboxes again.

 

[combo]

There is no way to keep objects linked to cell. The best you can do is to move objects to a position defined by cell location. An example (sheet's module) to move control to active cell:

Private Sub CommandButton1_Click()
With Me.CheckBox1
    .Left = ActiveCell.Left
    .Top = ActiveCell.Top
End With
End Sub

BTW, activex controls, even MSForms, do not behave well on the worksheet, you may consider data validation in cell with TRUE/FALSE options instead.


combo

 

[DSaba10]

That was perfect. Ran through the debugger and also noticed that using TypeName was completely wrong, changed that to chkBox.Name and everything evaluated and worked as planned.

I agree that it'd be easier to just use something simpler that isn't an activex control, but I'm confined to what the boss wants.

I appreciate the help!