CODEWITHSUNDEEP
excelvba
Selma Espinoza
Selma Espinoza

Reputation: 31

Does a cell contains a CheckBox?

I have two types of checkboxes.

Each type of checkbox is defined in its' column :

I want the second type of checkboxes describing my instruction about a sum, as long as the data to add are set in rows between two cells where my first type of checkboxes ("checkboxMacro"&i) are.

I tried this but it seems that "TypeOf" is wrongly used.

How can I stop to the correct row using the fact we have to stop when the cell contains a checkbox and not a number or Nothing?

Sub SommerTest()
    Dim i As Byte
    For r = 1 To 178
        If TypeOf Sheets(2).Cells(r, 3) Is Object  Then
            For i = r + 1 To 178
                While IsNumeric(Sheets(2).Cells(i, 3)) _
                            And (Sheets(2).Cells(i, 3).Value <> "")
                    With Sheets(2)
                    If .OLEObjects("CheckBox" & i).Object.Value = True _
                           And ThisWorkbook.Worksheets(2).Cells(i, 4)="" Then
                        For Each j In Array(7, 8, 9, 10, 15, 16, 17)
                           ThisWorkbook.Worksheets(2).Cells(r, j).Value = _
                             ThisWorkbook.Worksheets(2).Cells(r, j).Value _
                             +(ThisWorkbook.Worksheets(2).Cells(i, j).Value) _
                             *(ThisWorkbook.Worksheets(2).Cells(i, 3).Value)
                            ThisWorkbook.Worksheets(2).Cells(i, 4).Value _
                                    = "Sélectionné"
                        Next j
                      ElseIf .OLEObjects("CheckBox" & i).Object.Value = False _
                                And ThisWorkbook.Worksheets(2).Cells(i, 4) _
                                        = "Sélectionné" Then
                          For Each j In Array(7, 8, 9, 10, 15, 16, 17)
                            ThisWorkbook.Worksheets(2).Cells(r, j).Value = _
                              ThisWorkbook.Worksheets(2).Cells(r, j).Value _
                              -(ThisWorkbook.Worksheets(2).Cells(i, j).Value) _
                              *(ThisWorkbook.Worksheets(2).Cells(i, 3).Value)
                            ThisWorkbook.Worksheets(2).Cells(i, 4).Value = ""
                          Next j
                      End If
                    End With
                Wend
            Next i
        End If
    Next r
End Sub

Upvotes: 3

Views: 151

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

As @Rory says, You could investigate around the topleftcell property, not sure on it's accuracy in relation to the size of the control, but something like

.shapes("CheckBox21").oleformat.object.topleftcell.address

.oleobjects("CheckBox21").topleftcell.address

Dim s as shape

for each s in activesheet.shapes
    if s.oleformat.object.topleftcell.address=activecell.address.......

    end if
next s

Hope this helps.

Upvotes: 3

Related Questions