I have a script which changes the checkstate of two checkboxes in an excel sheet. The full procedure is:
- Opening an Excel template file
- Filling out some data, one of that steps is using the sub below
- 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