Changing label captions in UserForm while code is running.

[Skippy]

I'm calling a subroutine and while the code is running, I would like to inform the user of what is happening. So I have a small UserForm that pops up and I want to change the caption of a label. The problem is that once I invoke the form, the code stops running. I suspect I need a DoEvents, but am not sure how to use it. The following is a snippet of my code:

'**** CALL TO THE SUB WITH USERFORM POP_UP ****

Private Sub cboFrom_Change()
    cboProdGrp.Clear
     FillComboProdGrp
    cboProdGrp.ListIndex = -1
End Sub


"**** SUB WITH USERFORM POP_UP ****

Sub FillComboProdGrp()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

frmMsg.Show
' [CODE STOPS RUNNING HERE]
frmMsg.lblMsg.Caption = "Applying advanced filter."

' CODE INSERTED HERE

frmMsg.lblMsg.Caption = "Removing duplicates."
  
 ' CODE INSERTED HERE 

frmMsg.lblMsg.Caption = "Sorting results."

 
' CODE INSERTED HERE

frmMsg.Hide

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

 

[Richard Schollar]

Hi

This is untested I'm afraid, but worth a shot - try displaying the userform in a modeless state:

amend

frmMsg.Show

to

frmMsg.Show vbModeless

This will only work if you are using Excel2000 or later.

Best regards

Richard

 

[Skippy]

Parsnip,

Thanks for the feedback. I tried your suggestion and a couple of things occured. First, I have another userform showing and it was necessary to specify it as vbModeless as well:

frmMainForm.Show vbModeless

.If this is not specified, there is an error message indicating a conflict between a vbModal and a vbModeless form. Having done this, the code runs OK but the frmMsg remains "ghosted" (i.e. can only see it's outline but nothing of the form itself, which is a white blank space).

 

[Skippy]

Turns out I needed to apply Repaint along with Parsnip's suggestion for setting the form as vbModeless. Many thanks Parsnip!

Sub FillComboProdGrp() 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 

frmMsg.Show vbModeless
frmMsg.lblMsg.Caption = "Applying advanced filter." 
frmMsg.Repaint

' CODE INSERTED HERE 

frmMsg.lblMsg.Caption = "Removing duplicates." 
frmMsg.Repaint
  
 ' CODE INSERTED HERE 

frmMsg.lblMsg.Caption = "Sorting results." 
frmMsg.Repaint  

' CODE INSERTED HERE 

    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 

frmMsg.Hide
End Sub

 

[Richard Schollar]

Skippy

Thanks for posting the complete solution - I was not aware of the repaint method, so this will prove useful in my own coding

Best regards

Richard