Checkbox control checked unchecked

[Jeroen Kluytmans]

Hello,

I have around 1750 checkboxes in a sheet (250 lines with 7 checkboxes
each from E8:K8) Now I want to be able to delete a row in this region,
the checkboxes related to that row are however not deleted. I want to
shift all the checkmarks in the checkboxes below one roe of checkboxes
up. I tried to do this with format control of the checkboxes somewhere
else in the sheet, but these adresses change if a row is deleted. Does
anybody have a hint how to tackle this problem?

Kind regards,
Jeroen Kluytmans

 

[pikus]

Name the checkboxes in such a way that you will easily be able to sa
which CBs are in each row. For instance, for row 52 you could d
something like cb0521 through cb0527. THEN you do this before deletin
the row:

rowToDelete = 52
For x = 1 To 7
Worksheets("Sheet1").Controls("cb" & Format(rowToDelete, 000)
x).Delete
Next x

Then delete the row.

- Piku

 

[Jeroen Kluytmans]

Pikus,

Pikus wrote:

rowToDelete = 52
For x = 1 To 7
Worksheets("Sheet1").Controls("cb" & Format(rowToDelete, 000) &
x).Delete
Next x

When I do this I get the message

Object doesn't support thisproperty or method

Any clue what's wrong?

Jeroen

 

[Dave Peterson]

What kind of checkboxes are they--from the forms toolbar or from the control
toolbox toolbar?

If you did name them nicely (_cb0521 through _cb0527), you could use Picus's
suggestion:

for checkboxes from the Forms toolbar:

rowToDelete = 52
For x = 1 To 7
Worksheets("Sheet1").checkboxes("_cb" & Format(rowToDelete, "000") & x).Delete
Next x

from the control toolbox:

rowtodelete = 52
For x = 1 To 7
Worksheets("Sheet1").OLEObjects("_cb" & Format(rowtodelete, "000") & x).Delete
Next x

Notice that I added an underscore to the name _cb0521 through _cb0527. (CB0521
looked too much like a range address for excel to allow that as the name.)

But if you didn't name them nicely, you could look at where their located:

This has code for both Forms and control toolbox checkboxes:

Option Explicit
Sub deleteRowAndCBX(myRow As Long, Optional wks As Worksheet)

Dim myCBX As CheckBox
Dim oleobj As OLEObject

If wks Is Nothing Then
Set wks = ActiveSheet
End If

For Each myCBX In wks.CheckBoxes
With myCBX
If .TopLeftCell.Row = myRow Then
.Delete
End If
End With
Next myCBX

For Each oleobj In wks.OLEObjects
With oleobj
If TypeOf .Object Is MSForms.CheckBox Then
If .TopLeftCell.Row = myRow Then
.Delete
End If
End If
End With
Next oleobj

wks.Rows(myRow).Delete

End Sub

Sub testme()
Call deleteRowAndCBX(6)
End Sub