33708

How do delete all checkboxes in a range of cells

Question:

I have code to delete a checkbox in a certain cell but I need it to delete all checkboxes in a range I have selected. Following is the code I have that deletes a checkbox in a certain cell.

Columns("B:B").Select Selection.Find(What:="FIELD SERVICES", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(1, -1).Select Dim CB8 As CheckBox For Each CB8 In ActiveSheet.CheckBoxes If CB8.TopLeftCell.Address = ActiveCell.Address Then CB8.Delete Next

Following is how I tried to alter it to delete cells in the range I need but it only deletes the checkbox in the first cell of the range.

Columns("B:B").Select Selection.Find(What:="FIELD SERVICES", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate Range(ActiveCell.Offset(1, -1), ActiveCell.Offset(8, 0).Select Dim CB8 As CheckBox For Each CB8 In ActiveSheet.CheckBoxes If CB8.TopLeftCell.Address = ActiveCell.Address Then CB8.Delete Next

Any advice is greatly appreciated.

Answer1:

Dim f as Range, cbRange as range Dim CB8 As CheckBox Set f = Columns("B:B").Find(What:="FIELD SERVICES", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart) if not f is Nothing then set cbRange = f.parent.range(f.Offset(1, -1), f.Offset(8, 0)) For Each CB8 In ActiveSheet.CheckBoxes If not application.intersect(CB8.TopLeftCell, cbRange) is nothing Then CB8.Delete Next end if

Recommend

  • How can I filter my searchresults with multiple filters? (dropdown, keywords etc)
  • Getting maximum row/column number from Excel Range
  • Select column and find the value from other cell
  • Last column of a specific row in Excel
  • setting default value of superglobal
  • I'm trying to add a count to the number of searches found in my code
  • Trigger function on Enter keypress
  • Java error in FireFox - myApplet.myFunction is not a function
  • Excel VBA to delete blank rows within a range
  • Can JQuery.Validate plugin prevent submission of an Ajax form
  • Excel VB, find and move row to bottom of list separate with new heading
  • How to run the same code on all the worksheets in an Excel file
  • Flask - Use the same view to render a search form and then search results
  • excel vba replace failure
  • Google Sheets script - get value from radio button, currently undefined
  • how to append data to existing excel file using vb.net?
  • find dynamic variable code giving runtime error 91
  • How to prevent Excel from crashing when renaming a sheet in a workbook with UDFs
  • To Add Header and Footer for many word documents?
  • VBA - syntax for .pictures.insert incorrect? Insert method of picture class failed
  • Excel VBA : assign formula to multiples dynamic range table in same sheet
  • Excel - Macro to create chart based on selection
  • Excel VBA Replace with Array Groups
  • Sort without moving formatting
  • VBA find function getting error 424 object required [duplicate]
  • Creating an array variable with a variable number of elements
  • How to split itemscope over multiple HTML elements?
  • Excel VBA - Using Find method on a range of dates
  • Does as-if rule prevent compiler reordering of accesses to global/member variables?
  • Symfony2 Form is always empty after submitting
  • Multiprocessing in Python: how to implement a loop over “apply_async” as “map_async” using a callbac
  • After converting my FBX file to a .gltf, the model is incredibly small, why?
  • How to convert from System.Drawing.Color to Excel.ColorFormat in C#? Change comment color
  • How to model a transition system with SPIN
  • Cant find why the layout is getting smaller
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs