VBA: Label Update = Flicker

[Cerebro]

Hello all,

I have a bit of a problem and hope you can help me. First of all, I'm not that much of an VBA expert, so please bear with me and use simple explanations.


I have a userform with a picture in the backround. On that picture are many labels with numbers as caption as well as spin buttons. The spin buttons change those labels and while this works pretty well, there is one specific spin that changes a lot of labels at the same time. This causes massive flickering of all affected labels and I'm unable to get rid of it.

I noticed that the flickering is reduced a bit if I change the label backstyle to opague or get rid of the backround picture, but that's not much of an option. Is there any way to get rid of that pesky flickering?


Thanks in advance.

 

[Jaafar Tribak]

Welcome to Mrexcel

Have you tried setting the Labels backstyle Property to Transparent ?
Edit : Sorry I misread your question

Maybe a this needs using the windows API .. What version of excel and windows are you using ?

 

[Cerebro]

Hi Jaafar,

thanks for the reply. I'm using Windows 8 (will update to 10, as soon as it's released) and Excel 2013.

 

[Jaafar Tribak]

Have you tried using Me.Repaint at the end ie: right after changing the labels ? I have tried it with transparent labels and the ugly labels flickering is gone ... However the flickering now happens with the userform client area .. it is not the perfect fix but it is better

 

[Cerebro]

Well, I tried it now, but it seems to make the flicker even worse and all across the userform.

Is there any other way? Maybe a workaround would be to slightly slow down the code? I noticed the flickering starts if 9 or 10 Labels are adjusted at once. My spin button changes about thrice the amount. Could I just place some milliseconds wait time in certain key spots?

Here is an example of my code:

Private Sub ASpin11_Change() ' Intelligenz

If Status.Caption = "X" Then

        AInt.Caption = SInt.Value: LInt.Caption = 8 + AInt.Caption + VInt.Caption: Call AGP
Else
        VInt.Caption = SInt.Value: LInt.Caption = 8 + AInt.Caption + VInt.Caption: Call VGP
End If
        Call FSpin11_Change: Call FSpin12_Change: Call FSpin13_Change: Call FSpin14_Change: Call FSpin16_Change: Call FSpin17_Change: Call FSpin18_Change: Call FSpin19_Change: Call FSpin20_Change ' -- Things are fine until here

' -- maybe put some wait time in here? (how?)

        Call FSpin21_Change: Call FSpin22_Change: Call FSpin23_Change: Call FSpin25_Change: Call FSpin26_Change: Call FSpin27_Change: Call FSpin28_Change: Call FSpin30_Change: Call FSpin31_Change: Call FSpin33_Change ' -- after FSpin21 things start flickering

End Sub

(Those FSpin_Change Macros are all the same. To simplify it: They take the value of the associated spinner and put it on the Label.)

 

[Jaafar Tribak]

I think we have a solution
I never thought that using DoEvents would remove all the flickering in this scenario !

This worked for me for all transparent labels :

Private Sub SpinButton1_SpinDown()
    Dim ctl As Control
    For Each ctl In Me.Controls
    If TypeName(ctl) = "Label" Then
        ctl.Caption = ctl.Caption - 1
        DoEvents
    End If
    Next
End Sub

Private Sub SpinButton1_SpinUp()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If TypeName(ctl) = "Label" Then
            ctl.Caption = ctl.Caption + 1
            DoEvents
        End If
    Next
End Sub

 

[Cerebro]

YES! To think that one simple "DoEvents" before all those Call Macros would solve this thing. Perfect.

Well, I have to test this again this evening on my computer at home, because I have a slightly different situation there, but I'm quite confident it will work. Will give a quick heads-up then.

For now: Thank you SO MUCH for helping me. This thing got me crazy for the last day or so.

 

[Cerebro]

Ok, to close this: I got to test this at my pc at home and it works like a charm. Simple but perfect solution. Thanks again for the help!