Changing label captions in UserForm while code is running.

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
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:
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

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

amend

frmMsg.Show

to

Code:
frmMsg.Show vbModeless

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

Best regards

Richard
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
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:
Code:
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

Board Regular
Joined
Mar 3, 2002
Messages
194
Turns out I needed to apply Repaint along with Parsnip's suggestion for setting the form as vbModeless. Many thanks Parsnip! :biggrin:
Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Skippy

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

Best regards

Richard
 

Watch MrExcel Video

Forum statistics

Threads
1,118,670
Messages
5,573,560
Members
412,537
Latest member
Mohamed_5966
Top