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.

Try to repair/reinstall MS Office on machine where problem persists. — Maciej Los, Aug 31 '21 at 07:42
On the other hand, try to move the code to userform - `Initialize` procedure. Let me know if it helps. — Maciej Los, Aug 31 '21 at 07:45
@MaciejLos both suggestions do not help, the error occurs in any procedure (note that the error only occurs after assigning a cell value to a variable, it works before that) — Albin, Aug 31 '21 at 16:27
@Albin Using the structs from https://stackoverflow.com/a/59312070/11683, please test what binary content the returned bool has when the test fails. (Reverse the sample: assign `UserForm1.CheckBox1.Font.Strikethrough` to `b1.b`, `LSet i1 = b1`, test `i1.i`.) — GSerg, Aug 31 '21 at 16:48
@Albin And for the second line of investigation, please dump and compare `ObjPtr(UserForm1)` and `ObjPtr(UserForm1.CheckBox1)` in each of those 6 tests. — GSerg, Aug 31 '21 at 16:54
@Albin today is the day we must decide who gets what part of the bounty — Hovercraft Full Of Eels, Sep 05 '21 at 11:57
@HovercraftFullOfEels sorry, I was gone for the weekend, I'll check the answers tonight, we still have the 24 hour grace period, right? — Albin, Sep 05 '21 at 17:07
So far, none of the answers gave me the correct answer, feel free to give the bounty to whomever you like. — Albin, Sep 06 '21 at 21:50
You were too late getting back to me, and so I have wasted 50 reputation points on this. — Hovercraft Full Of Eels, Sep 07 '21 at 20:39
This is not the end of the world, but it does leave a bad taste and will probably leave me less willing to do this sort of thing for other people in the future, which is not good for the site. — Hovercraft Full Of Eels, Sep 07 '21 at 20:43

score 1 · Answer 1 · edited Sep 06 '21 at 20:48

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.

Thanks, I have to check if it's possible to to change the value back to false after I set the value to true on purpose. If the workaround leads to the problem that the value can only be changed once unfortunately it won't help me in the long run. — Albin, Aug 31 '21 at 16:51

iѕєρєня · Answer 2 · 2021-09-04T19:22:04.620

0

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!

edited Sep 04 '21 at 19:22

answered Sep 04 '21 at 18:40

iѕєρєня

524
4
14

score 0 · Answer 3 · answered Sep 05 '21 at 10:44

0

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.

answered Sep 05 '21 at 10:44

Ivan

1,274
16
22

I tried it without opening and closing a from. a form opens and runs the code and the issue still exist. it seems when you address a sheet with its name something like what you said happens, but isn't it suppose to do what we write? all the code said was `CheckBox1.Font.Strikethrough = False` and even its repeated but it has to set it to false regardless of what other code we have or even unloading or loading a from. – iѕєρєня Sep 05 '21 at 21:07
I haven't opened the form at all, it was just initialized by setting the caption of one of it's elements (the checkbox). My goal is to set certain properties (in this case the font format of a checkbox) of the form depending on the value of certain cells in the table. But I'm unable to do this because once I read the cell value the format of the checkbox does not work properly anymore. – Albin Sep 06 '21 at 22:02

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

3 Answers3