×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel 2013 VBA Moving ActiveX checkboxes
2

Excel 2013 VBA Moving ActiveX checkboxes

Excel 2013 VBA Moving ActiveX checkboxes

(OP)
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 --> VBA

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 --> VBA

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

RE: Excel 2013 VBA Moving ActiveX checkboxes

(OP)
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.

RE: Excel 2013 VBA Moving ActiveX checkboxes

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.

RE: Excel 2013 VBA Moving ActiveX checkboxes

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

RE: Excel 2013 VBA Moving ActiveX checkboxes

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close