Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modeless Userform Not Displaying
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modeless Userform Not Displaying
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 "tc69" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modeless Userform Not Displaying
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 "tc69" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modeless Userform Not Displaying
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modeless Userform Not Displaying
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" wrote: 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 "tc69" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modeless Userform Not Displaying
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 "tc69" wrote in message ... 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" wrote: 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 "tc69" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modeless userform problem | Excel Programming | |||
Modeless userform | New Users to Excel | |||
Modeless Userform problem | Excel Programming | |||
modeless userform | Excel Programming | |||
Modeless userform | Excel Programming |