Member Avatar for Gizzle72

Very new to writing macros, trying to impress the boss with a project, but I am stuck with a code. I can hide the rows when the checkbox is selected, but the other checkboxes are still visible. Is there a way to hide other checkboxes as well. My current code:
Private Sub CheckBox20_Click()
If CheckBox20.Value = True Then
Range("A64:P82").EntireRow.Hidden = True
End If
If CheckBox20.Value = False Then
Range("A64:P82").EntireRow.Hidden = False
End If
End Sub

I want to hide CheckBox22 - CheckBox34.

  • 2 Contributors
  • 2 Replies
  • 248 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Gizzle72

Dani AI

Generated

Most likely cause: the checkboxes are not set to "move and size with cells" (or they are a different control type), so hiding rows leaves the controls visible. ’s macro correctly hides the rows, and ’s With-block is fine for copying cell values, but that doesn’t affect control placement/visibility. Also note the syntax error in Post #3 — the expression .Range("E82").Value = .Range("G82").Value = .Range("I82").Value is invalid; assignments must be done one target at a time.

Fast manual fix

  • For Form controls: right-click → Format Control → Properties → choose "Move and size with cells".
  • For ActiveX/OLE controls: right-click → Format Object (or open the Selection Pane) → Properties → set placement to move/size with cells.
    Using the Selection Pane (Home → Find & Select → Selection Pane) makes it easy to find control names and hide or group many at once.

Programmatic options (works from a standard module)

  • Toggle ActiveX/OLE checkboxes by name (skips missing ones):
Sub ToggleGroup_OLE(masterState As Boolean)
    Dim i As Long
    For i = 22 To 34
        On Error Resume Next
        ActiveSheet.OLEObjects("CheckBox" & i).Visible = Not masterState
        On Error GoTo 0
    Next i
End Sub
  • Toggle Forms checkboxes (shape-based):
Sub ToggleGroup_FormControls(bVisible As Boolean)
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.Type = msoFormControl And shp.FormControlType = xlCheckBox Then
            shp.Visible = IIf(bVisible, msoTrue, msoFalse)
        End If
    Next shp
End Sub

Troubleshooting tips

  • Use a small helper to list control names: iterate ActiveSheet.OLEObjects or ActiveSheet.Shapes and Debug.Print names to confirm exact names before addressing them in code.
  • If mixing control types, either set placement to xlMoveAndSize for both OLEObjects and Shapes, or handle each type with the appropriate routine.
  • Avoid leaving On Error Resume Next active without follow-up error handling — use it only to skip nonexistent names and then reset error handling.

These steps let the checkboxes hide with rows (or be hidden explicitly), and also fix the assignment bug seen in Post #3 so cell-copy operations work as intended.

Recommended Answers

All 2 Replies

Member Avatar for harryhyden

You need to use the following methods:

With ActiveSheet
.Range("C7").Value = .Range("C8").Value
.Range("D7").Value = .Range("D8").Value
.Range("E7").Value = .Range("E8").Value
.Range("F7").Value = .Range("F8").Value
End With

Member Avatar for Gizzle72

I have tested the macro and the checkboxes still appear when the rows are hidden:
Private Sub CheckBox20_Click()
If CheckBox20.Value = False Then
Range("A64:P82").EntireRow.Hidden = True
End If
If CheckBox20.Value = True Then
Range("A64:P82").EntireRow.Hidden = False
End If
With ActiveSheet
.Range("E65").Value = .Range("F65").Value
.Range("E70").Value = .Range("F70").Value
.Range("E73").Value = .Range("F73").Value
.Range("E76").Value = .Range("F76").Value
.Range("E79").Value = .Range("F79").Value
.Range("E82").Value = .Range("G82").Value = .Range("I82").Value
End With
End Sub

Perhaps I have written it incorrectly? Should it be two separate macros or is one as I have done okay?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.