mardi 10 octobre 2017

Excel - Send a msgbox if all the checkboxes (ActiveX) are unchecked

I'm trying to make the macro send a msgbox in case any checkbox of the activesheet (ActiveX) is checked.

I dont know why, but when I run the macro it jumps the line "x = x + 1" even if I have a checkbox checked...

Can someone help me?

Private Sub adecoagrobox1_Click()


If adecoagrobox1.Value = True Then
    On Error Resume Next
    ThisWorkbook.Sheets("Comps_pivot").PivotTables("compspivot1").AddDataField Sheets("Comps_pivot").PivotTables( _
    "compspivot1").PivotFields("Adecoagro"), "Adecoagro ", xlSum
Else
    On Error Resume Next
    ThisWorkbook.Sheets("Comps_pivot").PivotTables("compspivot1").PivotFields("Adecoagro "). _
    Orientation = xlHidden

End If

Dim control as OLEObject
Dim x as integer
x = x + 1

  For Each control In ActiveSheet.OLEObjects
If TypeName(control.Object) = "Checkbox" And control.Object.Value = 1 Then
 x = x + 1
End If
Next control

If x = 0 Then
MsgBox ("You must select at least one option")
On Error Resume Next
adecoagrobox1.Value = True
ThisWorkbook.Sheets("Comps_pivot").PivotTables("compspivot1").AddDataField Sheets("Comps_pivot").PivotTables( _
    "compspivot1").PivotFields("Adecoagro"), "Adecoagro ", xlSum

End If


End Sub

Many thanks in advance!




Aucun commentaire:

Enregistrer un commentaire