Remove Excel Checkbox control with Powershell RRS feed

  • Question

  • I would like to remove Excel checkboxes from an Excel spreadsheet so that the spreadsheet can work with Excel online.  I have found scripts to remove rows and sheets, but can't find out about controls.

    Thanks for any help.

    Wednesday, December 16, 2020 4:28 PM

All replies

  • re:  remove all checkboxes

    If you don't want to keep any other objects on the worksheet, you can remove all objects by...
    1.  Ribbon | Home (tab) | Editing (group) | Find & Select
         (an older ribbon version)
    2. Choose: Go to Special | Objects

    3. Tap the delete key.


    Custom_Functions Excel add-in (free)
    20+ custom functions
    Download from OneDrive


    Wednesday, December 16, 2020 10:53 PM
  • I was hoping to do it with Powershell because there are 2000 excel files that used the same template.  Carpal Tunnel avoidance :)
    Thursday, December 17, 2020 7:25 PM
  • Re:  code not clicks

    My PowerShell is limited, the following is VBA.

    Note:  Assumes Checkboxes are not Active X controls.
              Open all the workbooks you can keep track of  and run the following code.
              (place your cursor within the code and tap the F5 key one time)

    Sub RemoveCheckBoxesOnly()
    'Nothing Left to Lose - December 2020
     On Error GoTo WrongBox
     Dim Shp As Excel.Shape
     Dim WS As Excel.Worksheet
     Dim WB As Excel.Workbook
     Dim BoxCount As Long
     Application.ScreenUpdating = False
     For Each WB In Application.Workbooks
     For Each WS In WB.Worksheets
     For Each Shp In WS.Shapes
       If Shp.Type = msoFormControl Then
         If Shp.FormControlType = xlCheckBox Then
           BoxCount = BoxCount + 1
         End If
       End If
     Next 'Shp
     Next 'WS
     Next 'WB
     Application.ScreenUpdating = True
     Application.Cursor = xlDefault
     VBA.MsgBox BoxCount & " checkboxes removed.  ", _
     vbOKOnly, "Remove Checkboxes"
     Exit Sub
     Application.ScreenUpdating = True
     Application.Cursor = xlDefault
     VBA.MsgBox Err.Number & " - " & Err.Description & "   ", _
     vbCritical, "Checkbox Error Report"
    End Sub

    Professional_Compare workbook (free)
    (can also Clean, list uniques/common items, create a Benford Distribution and more)
    Download from OneDrive

    Thursday, December 17, 2020 11:02 PM