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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,380
Messages
6,130,285
Members
449,570
Latest member
TomMacca52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top