Excel checkbox value property can't be set in rare occasions

Viewed 71

I have a script which changes the checkstate of two checkboxes in an excel sheet. The full procedure is:

  1. Opening an Excel template file
  2. Filling out some data, one of that steps is using the sub below
  3. Saving the file at a specific location

The file template therefore is always the same. On some occasions (<5% of the time), the routine fails in the sub below in the first of the two lines sh.ControlFormat.Value = -4146 with an error

Microsoft Excel: Die Value-Eigenschaft des CheckBox-Objektes kann nicht festgelegt werden.

which probably is the translation of the English error message

Unable to Set the Value property of the CheckBox Class

If that happens, you can close the file, start the exact same routine again and it so far for me has always worked then. Since I am running through all shapes, checking their type, checking their form-control-type and then checking the individual name, I am unsure how this is still failing since the file is obviously open, accessible and the correct form-control-element has been found.

Any idea how to mitigate this? I'd be happy with any insight what might cause such an error in order to find a way around it. Ideally something working better than simply detecting it and telling the user that something has gone wrong, please close excel and try again.

Sub modify_ci(oExcel, inp_cib)
    
    Set sheet = oExcel.ActiveWorkbook.ActiveSheet
    
    For Each sh in sheet.Shapes
        If sh.Type = 8 Then ' 8 => msoFormControl
            If sh.FormControlType = 1 Then ' 1 => xlCheckBox
                If(sh.Name = "chkbox_ci_yes") Then
                    If(inp_cib = "true") Then
                        sh.ControlFormat.Value = 1
                    Else
                        sh.ControlFormat.Value = -4146
                    End If
                ElseIf(sh.Name = "chkbox_ci_no") Then
                    If(inp_cib = "false") Then
                        sh.ControlFormat.Value = 1
                    Else
                        sh.ControlFormat.Value = -4146
                    End If
                End If
            End If
        End If
    Next
End Sub
0 Answers
Related