Excel vba - Refresh automatically a label on an excel sheet

Question

I'm trying to update automatically a label located directly in an Excel sheet (not containing a form) via an vba script.

My Excel sheet contain a lot of text and steps to do for the job to do. Each step has it own button and label. That's why I decided to put a label after each button calling its own function.

After clicking the button, the function is executed and the label changes several times during the execution of the script. Nevertheless my label is not updated during the execution. It is updated only at the end (when the execution is completed) with the last value defined.

I tried this lines:

DoEvents

Or

Application.ScreenUpdating = True

But it's not working.

Here my script:

Private Sub ButtonExcelToWord_Click()

Me.Label.Caption = "Exécution du script..."

Application.ScreenUpdating = True
DoEvents

Me.Label.Caption = "Mise à jour..."

Application.ScreenUpdating = True
DoEvents

.......
End Sub

Could yo please tell me how to do that, and if it's possible to do that without doing a form?

Thanks

Is there in the active sheet a label named 'Label'? If yes, is 'ButtonExcelToWord' an ActiveX control having the code in the sheet module where the 'Label' label exists? — FaneDuru, Apr 14 '21 at 12:03
@FaneDuru the label is in the active sheet at the buton. The buton is an ActiveX control — coeurdange57, Apr 14 '21 at 12:40
If so (the label is named 'Label'), after your code attempt finishes, are you able to manually change the involved label (named 'Label') caption? I cannot reproduce your problem. What Excel version do you use? — FaneDuru, Apr 14 '21 at 12:45

score 0 · Answer 1 · answered Apr 14 '21 at 11:54

0

You should use StatusBar :) here is example.

answered Apr 14 '21 at 11:54

bankeris

181
10

Tanks for your help. But I cannot use the Status Bar (security policy rules). My Excel sheet contain a lot of text and steps to do for the job to do. Each step has it own label. That's why I decided to put a label after each button calling its own function – coeurdange57 Apr 14 '21 at 12:05

score 0 · Accepted Answer · answered Apr 14 '21 at 12:18

Writing DoEvents twice should do the trick:

Sub TestMe()
    
    Application.ScreenUpdating = True
    Dim i As Long
    For i = 1 To 5
        Me.Label1.Caption = i
        DoEvents
        DoEvents
        Application.Wait Now + #12:00:01 AM#
    Next i
    
End Sub

Excel vba - Refresh automatically a label on an excel sheet

2 Answers2