Changing a CheckBox's Font Attribute "Strikethrough" from a specific module does not work

Question

Using VBA (Excel 365 (16.0.12527.20880), German) I'm trying to set the strikethrough property of a checkbox (Checkbox1) caption to False in accordance to the Office VBA reference this should work.

The following code is placed in a Module1 (simplified) and changes the strikethrough-value of UserForm1.Checkbox1 (UserForm1 and Checkbox1 is static, created via the VBA-Editor, not via code during runtime).

    Sub ChangeCheckBox()

    UserForm1.CheckBox1.Caption = "Test" 'this triggers the Init-Procedure, which only sets the Height and Width of the UserForm. This shouldn't effect the problem, but I'm mentioning it here so it's clear that the form has been initialized. But if I leave it out, it's the same problem.       

    'Pre-Test - works fine

        'Check initial value
        Debug.Print UserForm1.CheckBox1.Font.Strikethrough  'returns false (as it should)
        
        'Write value and check it
        UserForm1.CheckBox1.Font.Strikethrough = False
        Debug.Print UserForm1.CheckBox1.Font.Strikethrough  'returns false (as it should)

        'Write value and check it again
        UserForm1.CheckBox1.Font.Strikethrough = False
        Debug.Print UserForm1.CheckBox1.Font.Strikethrough  'returns false (as it should)

    'This next line seeems to cause the post-test failure

        tmpString = ThisWorkbook.Worksheets("Sheet1").Cells(tmpIndex, tmpColumn).Value
        Debug.Print tmpString     'returns the correct value

    'Post-Test - fails!!!

        'Check initial value
        Debug.Print UserForm1.CheckBox1.Font.Strikethrough  'returns false (as it should)

        'Write value and check it
        UserForm1.CheckBox1.Font.Strikethrough = False
        Debug.Print UserForm1.CheckBox1.Font.Strikethrough  'returns true (should still be false)

        'Write value and check it again
        UserForm1.CheckBox1.Font.Strikethrough = False
        Debug.Print UserForm1.CheckBox1.Font.Strikethrough  'returns true (definitely should return false now)

    End Sub

In my case, for some reason, the command sets the box to True instead of False. So far I pinpointed the problem to a specific line of code. The "Pre-Test" succeeds, the "Post-Test" fails (everything else works fine). Note, I'm using debug mode via step by step, no other code is executed "in-between".

The problem persists if I create a different Form and try the same thing there. The original procedures are several hundred lines of code, but I created the Testprocedure above and the problem persists. I can also reproduce it by creating a new Excel file from scratch with just one Sheet, one Userform/Checkbox, and one Module containing the test procedure.

So, why the hell does tmpString = ThisWorkbook.Worksheets("Sheetname").Cells(tmpIndex, tmpColumn).Value cause the "Post-Test" to fail?

Note: This error can not be reproduced on all machines, I tried it on a different machine. There, I can not reproduce the error.

Answer 1

I cannot reproduce the issue and I've tried on 3 different machines. However, I remember seeing a similar issue about 7 years ago and I managed to work around it by first checking if I need to change the value of the font property.

Try replacing all occurences of this:

UserForm1.CheckBox1.Font.Strikethrough = False

with this:

If UserForm1.CheckBox1.Font.Strikethrough Then UserForm1.CheckBox1.Font.Strikethrough = False

This basically sets the .Font.Strikethrough property to False only if it is already True because otherwise it is False (obviously) and if there is any bug in the setter of that property then this skips the bug.

---

Unrelated to your question but it's not a good idea to use the default instance of a Userform like you're doing here with Userform1.Checkbox.... I recommend you read this article written by @MathieuGuindon back in 2017.