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

user_2089396 0
Viewed 380

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.

excel-365 vba
3 Answers

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.

user_84889130

Try using index for your worksheet instead of the name.

Its a funny thing to happen in a programming language.

tmpString = ThisWorkbook.Worksheets(1).Cells(tmpIndex, tmpColumn).Value
    Debug.Print tmpString

It worked for me and I think because the polymorphic functions in VBA made without debugging! because nothing else could cause this kind of behavior!

user_48934390

I've managed to reproduce the issue the same way it occured to you. The root cause here is that you are not explicitly UNLOADING the form after you close it, which means that all modifications that you made to it while open get retained in memory. For example using your code, shows the checkbox with strikethrough even when running it 2nd time. Which means it's retained in memory.

So to fix this, simply add Unload UserForm1 in your procedure after running the code.

While this approach works in resetting the checkbox (which I assume is your ultimate goal here), it still returns true for those last 2 checks for some reason. To workaround that as well (though it could be unnecessary if you achieve your goal), then simply use @Cristian Buse's if/then solution.

user_30793180
Related