Excel VBA order/grouping of "Call"

Question

Probably not the best title, but it will be better with this exemple.

Here's a function inside a userform of a checkbox. I was trying to change the font to bold before the call to SelectAlldtList(which takes a few seconds to do).

What is happening is that the font doesn't change until after the SelectAlldtList is finished.

Private Sub CheckBoxAllDirectionTerritoriale_Click()
    If Me.CheckBoxAllDirectionTerritoriale.Value = True Then
        CheckBoxAllDirectionTerritoriale.Font.Bold = True
        Call SelectAlldtList
    Else
        CheckBoxAllDirectionTerritoriale.Font.Bold = False
        Call UnselectAlldtList
    End If
End Sub

If I add a Wait of 6 milliseconds, the behavior is the one I want. If I add a Wait of 5 milliseconds, the behavior is the same as without the Wait at all.

Private Sub CheckBoxAllDirectionTerritoriale_Click()
    If Me.CheckBoxAllDirectionTerritoriale.Value = True Then
        CheckBoxAllDirectionTerritoriale.Font.Bold = True
        Application.Wait (Now + 0.000006)
        Call SelectAlldtList
    Else
        CheckBoxAllDirectionTerritoriale.Font.Bold = False
        Application.Wait (Now + 0.000006)
        Call UnselectAlldtList
    End If
End Sub

Why is that? Is it something like without the Wait, SelectAlldtList is called before having time to bold and taking all the ressources?

While I'm at it, would there be a better way to do this? Some kind of synchronization?

score 0 · Answer 1 · answered Aug 05 '15 at 15:05

I'm not very experienced with what you have here, but I had a somewhat similar problem with a macro that zipped files and also needed a wait added to work correctly. Maybe what helped me could help you. Here's what I used to sync it:

Do Until Object1.Namespace(Variant1).items.Count = 1
    Application.Wait (Now + TimeValue("0:00:01"))
Loop

and here's how you might apply it (untested):

Private Sub CheckBoxAllDirectionTerritoriale_Click()
    If Me.CheckBoxAllDirectionTerritoriale.Value = True Then
        CheckBoxAllDirectionTerritoriale.Font.Bold = True
        Do Until CheckBoxAllDirectionTerritoriale.Font.Bold = True
            Application.Wait (Now + 0.000001)
        Loop
        Call SelectAlldtList
    Else
        CheckBoxAllDirectionTerritoriale.Font.Bold = False
        Do Until CheckBoxAllDirectionTerritoriale.Font.Bold = False
            Application.Wait (Now + 0.000001)
        Loop
        Call UnselectAlldtList
    End If
End Sub

I set it for one millisecond, but if you get some error with it being partially bolded or whatever, you might want to increase it to two. Note that you will still probably end up having the slight delay, but this way if it's 5, 7, etc. milliseconds you'll still be covered, I think.

Well it doesn't work for my situation, the code **never** enters the `Do Until` — Benoit, Aug 05 '15 at 15:31
Weird, it just skips straight over it into the `Call` line? Btw, did you know you don't need the word `Call` anymore, you can just put the function name? Sorry I couldn't be of more help. — puzzlepiece87, Aug 05 '15 at 18:47

Excel VBA order/grouping of "Call"

1 Answers1