Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2013 VBA Moving ActiveX checkboxes 2

Status
Not open for further replies.

DSaba10

Programmer
Nov 15, 2007
35
US
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:

Code:
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...
Code:
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
 
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.
 
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.
 
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:
Code:
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
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top