VBA Label Property is showing FALSE at the end of code-CodePudding

I am trying to update Label Caption property as a part of progress of code being executed to let user know what task is being performed. Everything works great until code is completed. In the end it is showing FALSE value instead of retailing all the message what was shown. How do I make sure it keeps all the message which was shown ? Forms!Main!Message.Caption = Forms!Main!Message.Caption is the one showing progress of message to the Label Control.

Here is sample code:

If strSource_System <> "" Then

    '--Download AP Data
    Forms!Main!.ctrlProgressBar.Value = Timer - startTime
    Forms!Main!Message.Caption = Forms!Main!Message.Caption & vbCrLf & "Downloading AP Data for System " & strSource_System & ". Please be patient..."
    SleepSec (1)
    Forms!Main!.ctrlProgressBar.Value = Timer - startTime
        Call Download_AP_Data(Source_CONNECTION, "SOURCE")
    Forms!Main!.ctrlProgressBar.Value = Timer - startTime
    'Reset Clock
    startTime = Timer
    Forms!Main!Message.Caption = Forms!Main!Message.Caption & vbCrLf & "AP Data Downloaded for System " & strSource_System & " Successfully."
    SleepSec (1)
    Forms!Main!.ctrlProgressBar.Value = Timer - startTime

End If

CodePudding user response：

You are appending all messages to the same label. The label's text might exceed its maximum length at some point and produce an error. If you have an On Error Resume Next, you will not see this error.

I suggest using a ListBox instead. This allows you to add the messages as new items into the ListBox. The Row Source Type of the ListBox must be Value List.

A Label can hold a maximum of 2,048 characters. A ListBox in ValueList mode can hold up to 64K characters (i.e., 32 times more).

If this is not enough, bind the ListBox to a table and add up to 64 records (i.e., up to 64k messages).

But you could also limit the number of messages displayed and start deleting the first messages when exceeding this limit to get a rolling display.

messageListBox.AddItem "Downloading AP Data for System " & strSource_System & ". Please be patient..."
DoEvents

And if you automatically want to scroll to the last item added, you can do this

' Scroll to the last item
messageListBox.SetFocus 'Next line does not work otherwise.
messageListBox.ListIndex = messageListBox.ListCount - 1

' Unselect it
messageListBox.Selected(messageListBox.ListCount - 1) = False

It's easier to extract this code to another Sub

Private Sub DisplayMessage(ByVal message As String)
    messageListBox.AddItem message
    messageListBox.SetFocus
    messageListBox.ListIndex = messageListBox.ListCount - 1
    messageListBox.Selected(messageListBox.ListCount - 1) = False
    DoEvents
End Sub

and then call it with

DisplayMessage "some message"

If you make this Sub public, then you can even place it in the Main form and then call it from the module with

Forms!Main.DisplayMessage "some message"

This has the advantage that this module does not have to know anything about a label or ListBox. The same could be done with a Sub ShowProgress.