locked
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.

    '---
    NLtL

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

    https://1drv.ms/u/s!Au8Lyt79SOuhZ_zj8wEtOjDcqAI?e=MrzfpA




    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
           Shp.Delete
           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
     
    WrongBox:
     Application.ScreenUpdating = True
     Application.Cursor = xlDefault
     VBA.MsgBox Err.Number & " - " & Err.Description & "   ", _
     vbCritical, "Checkbox Error Report"
    End Sub
    '---


    NLtL
    Professional_Compare workbook (free)
    (can also Clean, list uniques/common items, create a Benford Distribution and more)
    Download from OneDrive
    https://1drv.ms/u/s!Au8Lyt79SOuhZ_zj8wEtOjDcqAI?e=MrzfpA


    Thursday, December 17, 2020 11:02 PM