Modeless Userform Not Displaying

[Guest]

I have a UserForm with a single Label Control that I do a .Show vbModeless on
to display status messages. However, it only displays the the UserForm's
Title Bar and Borders. The contents appear as solid white, unless I'm in
Debug mode or I've done a MsgBox after I called .Show!?! Even then, as soon
as I proceed with execution it'll go back to the blank white contents. If I
choose not to .Hide it after I'm done with it, it'll show after my Macro has
stopped!?! I've played with Application.ScreenUpdating and .Interactive.
Suggestions? Thanks!

P.S. I don't want to use the Status Bar because it's not very noticeable and
I still want Excel's normal Status messages to show.

 

[Bob Flanagan]

Several suggestions (as I could not duplicate your problem; my form showed
fine)

Try unloading the form before showing it.
Try a Userform1.Repaint (if name is userform1)

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Peter T]

Typically would normally want to show the form as modal if it's merely to
display progress, but perhaps modeless depending on what you are doing. Try
something like this, limiting the number of times you update the form and in
particular use DoEvents to a minimum.

Sub test()
Dim frm As UserForm1, lab As MSForms.Label
Dim i As Long
Dim nTo As Long, nPct As Long, nNextUpdate As Long

nTo = 1000
Set frm = New UserForm1
frm.Show vbModeless
Set lab = frm.Label1
frm.gbCodeRunning = True

DoEvents

For i = 1 To nTo
Range("A1").Formula = "RAND()"
If i >= nNextUpdate Then
nPct = nPct + 1
nNextUpdate = nPct * nTo / 100
lab.Caption = nPct - 1 & "% done " & i & " \ " & nTo & " "
DoEvents
If Not frm.gbCodeRunning Then
Exit For
End If
End If
Next

frm.gbCodeRunning = False

frm.Hide
Unload frm ' not necessary with example as written but triggers QueryClose
Set frm = Nothing

End Sub

' in the form module
Public gbCodeRunning As Boolean

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If gbCodeRunning Then
' in case user clicks little x don't allow close form
Cancel = True

' maybe allow user to cancel
If MsgBox("Do you want to Cancel", vbYesNo) = vbYes _
Then gbCodeRunning = False
End If
End Sub

Regards,
Peter T

 

[Guest]

Thanks Bob! The UserForm.Repaint did it. Well, almost.

1) I have one message, "Re-Protecting Worksheets and Workbook", that only
gets to the "Wor" in "Worksheets", but it's much better than all blank. I
have a "Saving Workbook" message that does completely display.

2) It's still not Hiding while my Macro is still running though (before a
subsequent UserForm .Show and .Repaint, i.e. the latter shows on top of the
still displayed former until the Macro stops when both are cleared), but I
can live with that. I tried adding some extra UserForm.Repaint's after
UserForm.Hide to no avail.

I don't have any events defined for the UserForm or its Label Control nor am
I calling DoEvents. I'm simply doing a "UserForm.Show vbModeless" followed
by some code like my messages above indicate. Then I call "frmFlashMsg.Hide"
when I'm done with what my messages say I'm doing which is shortly, if not,
immediately before my Macro ends. I'm Unprotect'ing and Protect'ing 16
Worksheets and the Workbook and I'm saving about 1.5 Mb Workbook on a P4 2.2
Ghz with 480 Mb RAM running XP Pro Ver 2002 SP2 v5.1.2600 and Excel 2000
v9.0.2720. The re-protect takes just about a 1 sec. The save takes about
11-12 secs.

I'm thinking that since it's shown Modeless, the displaying or hiding of the
Form is not complete before UserForm .Show or .Hide returns and is running
concurrently with and competing against the subsequent VBA code and other
Windows apps for CPU time and losing. That would explain why it works when
I'm in Debug mode or viewing a MsgBox or back in Excel after my Macro has
stopped running since it's no longer competing for CPU time except with the
code that's waiting for me to type something.

This is the only thing in my UserForm:

' -- frmFlashMsg (As UserForm) - For Displaying a messages in a Modeless
Window

Option Explicit

Private Const DFLT_FONT = "Tahoma"
Private Const DFLT_FONT_SIZE = 8.25

' -- For Tahoma 8.25 Font used in Title
Private Const HZ_PTS_PER_CHAR = 92.25 / (7 + 3)
Private Const MIN_HZ_PTS = HZ_PTS_PER_CHAR * 2
Private Const MIN_VT_PTS = 35

Public Sub FlashMsg( _
ByVal sMsg As String, _
Optional ByVal sTitle As String = "", _
Optional ByVal eTextAlign As fmTextAlign = fmTextAlignLeft, _
Optional ByVal sFontName As String = DFLT_FONT, _
Optional ByVal dFontSizePts As Double = DFLT_FONT_SIZE, _
Optional ByVal lWaitSecs As Long = 0)

' -- Displays a modeless pop-up Window with sMsg and optional sTitle
' -- eTextAlign, sFontName, dFontSizePts and lWaitSecs. Auto-sizes
based on
' -- DFLT_FONT and DFLT_FONT_SIZE.

Dim lMinWinTitleWid As Long
Dim lTitleLen As Long

With olblMsg
.AutoSize = True
.WordWrap = False
.TextAlign = eTextAlign
.Font = sFontName
.Font.Size = dFontSizePts
.Caption = sMsg
End With ' -- olblMsg

Me.Caption = sTitle
Me.Height = olblMsg.Height + MIN_VT_PTS
Me.Width = olblMsg.Width + MIN_HZ_PTS

lTitleLen = Len(sTitle)
lMinWinTitleWid = (lTitleLen * HZ_PTS_PER_CHAR) + MIN_HZ_PTS +
HZ_PTS_PER_CHAR
If Me.Width < lMinWinTitleWid Then
Me.Width = lMinWinTitleWid
End If

Me.Show vbModeless
Me.Repaint

Application.Wait Now + TimeValue("0:00:" & lWaitSecs)

End Sub ' -- FlashMsg

 

[Guest]

Bob Flanagan's Repaint suggestion worked but only partially. Often it would
not finish displaying the message (about 20 characters).

I tried adding a single DoEvents call right after my Show vbModeless call
and it worked every time. Adding a single DoEvents call right after my Hide
call also helps it clear itself before subsequent code executes as well. I'm
not sure why you have a DoEvents call inside the loop below but I didn't find
it necessary. Like I said, my Form simply displays a message in a Label
control. It has no UserForm or Label Control Events implemented.

Thanks!

 

[Peter T]

I'm not sure why you have a DoEvents call inside

the loop below but I didn't find it necessary.

If your form to substitute the statusbar is merely to display something like
"Wait, processing...", indeed only the one DoEvents just after showing the
form is all you need. But if you want to inform user of progress, eg
percentage done, you will probably need DoEvents each time you write the new
update to the form within the loop. FWIW, changing the form's caption
wouldn't need DoEvents.

DoEvents is a relatively slow process, if you update the form with DoEvents
in each loop you might double (or more) the overall length of your routine.
However limiting to 100 updates, as in the example, would barely impact the
overall time of a long loop.

Be aware that DoEvents also enables user to trigger other processes within
the loop, a candidate is attempting to close the form and hence the trap in
the UserForm_QueryClose event in the example (though unlikely an issue with
your single DoEvents).

Regards,
Peter T