Excel 97 VBA screen updating (??)

[GSMike]

Does anyone know what it means when you show a user form on the screen, and when you move the form around with your mouse, it leaves a trail. It's like the screen behind is frozen from being updated.

Also, if you already have your VBA editor environment open when testing this, it appears to be in break mode (because the "Run" button in the toolbar is disabled and the "Reset" button is enabled), but there is not other evidence of it being "broken".

Specifically, I am trapping an error, unloading one user form and loading another. The new one loads fine, but the old is still on the screen (the "frozen screen" theory again, in that the form, I think is actually unloaded, but the screen just doesn't know it).

Thanks for any help you can give.

 

[Dreamboat]

Well, I'm not much of a coder, but I'd wager a guess that if you've got code that has turned off screen updating, you might want to turn it back on during the form swapping progress and then back off again when it runs the rest of your code. Am I making sense?
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[dsi]

As Dreamboat mentioned, try turning the screen updating ON.

Application.ScreenUpdating = True DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[GSMike]

Hi Dreamboat -
Thank you for your Tek-tip.
The issue is not one of screen updating, not in the literal sense anyway (as in, application.screenupdating=true). In a more general sense I guess it is, however.
What happens is strange to me at this point.
A command button prints a 36 page report. All goes well there. However, if the user clicks cancel while the report is printing, error #1004 occurs ("Print method...failed&quot. So, I trapped the error and tried to have the form with the print button on it unload, and a new form appear. The new form appears, but the old is still on the screen (but I feel that it probably really is unloaded). Also, when you drag the new form across the screen, it leaves a trail, like when you put that feature on your mouse, except in this case, the trail stays on the screen all the time (and doesn't go away like it does with the mouse).
Anyway, don't know if you have any other suggestions, but thought I'd give a little further insight into my problem.
Have a great weekend.

 

[dsi]

Are you loading Form2 from Form1?

Form1.bas
----------
Sub Button_Click()
    Me.Unload
    Form1.Show 1
End Sub
[code]

It may help if you could show the code you are using to load and unload your forms. At what point do you begin processing the Print command?

I am sure that we will get to the solution, eventually! DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Dreamboat]

I've emailed instructions to Mike on how to clean up his hard drive and told him to then run the code again. Now if it doesn't work, he can be sure it's the program or code and not a RAM/memory issue. I suggested he might also find this out by running the code on a different PC.

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[GSMike]

Thanks again, DSI. Here is the portion of the code where it all occurs. The print command button has about 200 lines, so I wouldn't want to put all that here. This is just the error handler. It handles the error that occurs when the user cancels the print while in process.

I've tried every combination of commands. Unload vs. hide, rearranging the order of the hiding and showing, repainting, worksheet.recalculate, unload the first form, select the worksheet, open the new form, repaint the new form...no success at all.

Here's a question that is probably an easy one: If you unload a form in the middle of a routine, does the portion of the routine after the unload command get dumped? Or does the form get unloaded only after the routine is finished (this assumes the routine is called FROM the form being unloaded). - bonus question..thanks

My forms are "SpecReports" and "PIFControlMenu".

If Err.Number = 1004 Then
    Cancel = True
    SpecReports.Hide
    PIFControlMenu.Show
    Me.Repaint
    Exit Sub
Else
    CancelCloseControl = True
    Cancel = True
    Me.Repaint
    MsgBox vbCrLf & "An error has occurred in the MTM Reporting Manager. Please get " _
    & "a screen shot of this message box (hit Print Screen or Alt+Print Screen) " _
    & "and e-mail the the screen shot to IS. Thank you for your cooperation." _
    & vbCrLf & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Error " _
    & "Description: " & Err.Description, vbOKOnly + vbCritical, "Please Report Error"
    SpecReports.Show
    PIFControlMenu.Show
    PIFControlMenu.Repaint
    Unload SpecReports
    Exit Sub
End If

 

[dsi]

When they hit the button to print, why don't you disable the Cancel button. Better yet, disable both buttons. Then, at the end of the print function, enable the buttons again. DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[GSMike]

Good suggestion..
This I don't know how to do..hmmm...it's the little box that pops up that says, "Printing page 1,2,3,...etc".
However, the report is 36 pages long. I want them to be able to cancel.
-Mike

 

[dsi]

Step 1:
Have you tried Dreamboat's suggestions yet?

Step ?:
Since you mentioned "worksheet.recalculate", I assume that you are doing this in Excel.

Where is this error handling routine? In the code for one of the forms or in your print function?

OK, let me see if I get this straight.

- You have a PRINT button on your form. (Form1)

- When the user hits the button, you call your print function. You have not unloaded (Form1) at this point.

- An Excel form appears, indicating that it is "Printing Page 1 ..."

- If the user does not hit Cancel, your print function finishes and focus is returned to (Form1).

- If the user hits cancel, you trap the error in the error handler that you posted above. Which, I assume, is in your Print Function (Not in the code for a form).

- If the error is trapped, and caused by the user hitting the Cancel Print button, you want to do what at this point?

Unfortuntely, there can be so many different problems that it is hard to pinpoint. Could you be explicit in your description of the problem. Completely describe what is happening, including form names. It will make it much easirer to pinpoint the problem. DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[GSMike]

DSI,
You are correct, this is Excel.
The progression of events is as follows:
1) The application opens to a user form "PIF Control Menu".
2) The user selects a command button, "Select Report to Print".
3) "PIF Control Menu" is hidden, "SpecReports" (form#2) is shown.
4) User selects report from "SpecReports" and clicks "Print".
5) If report is printed, "SpecReports" is hidden and "PIF Control Menu" is shown" when printing is finished.
6) If report print is canceled in process, error is trapped, and as in a complete print, "SpecReports" is supposed to be hidden (or unloaded) and "PIF Control Menu" is shown.

There is only a problem if in the case of #6 above. If the report is canceled, the the code runs without error. However, on the screen "SpecReports" is still visible behind "PIF Control Menu" ("SpecReports" is larger than "PIF Control Menu&quot and "PIF Control Menu has (apparently) put the application screen in some kind of frozen state (my best guess) because when I drag the form across the screen, it leaves a permanent trail. It will even cover the entire application if I keep dragging it. If I switch to another application, the strange screen state is ok. When I go back to Excel, the trail is still there. If I click "Work in Excel Mode" (a different command button) on this form ("PIF Control Menu&quot, the user form ("PIF Control Menu&quot is hidden and all of the worksheets in the file are made visible for the user browse. The screen is then cleared to be ok.

 

[dsi]

I think the problem is being caused because you are not unloading your forms. Here is a sample of how you can transfer control in your application.

In your main module:

Sub MainProgram()
    '...
    'Code before showing the PIF Control Menu
    '...
    
    'Show the PIF Control Menu
    frmPIFControlMenu.Show
    
    'Once the PIF Control Menu is closed,
    'control comes back here
    
    '...
    'Code after showing the PIF Control Menu
    '...
    End
End Sub

In the PIF Control Menu:

'SELECT REPORT TO PRINT
Private Sub btnSelectReportToPrint_Click()
    'Hide this menu (PIF Control) before showing
    'the Spec Reports menu
    Me.Hide       'still loaded in memory
    frmSpecReports.Show
    
    'Control comes back here when Spec Reports is closed
    'Show me again
    Me.Show
    
    'Control is left in this form
    
End Sub

'CLOSE PIF CONTROL MENU
Private Sub cmdClosePIFControl_Click()
    Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

In the Spec Reports form:

'PRINT SELECTED REPORT
Private Sub btnPrintReport_Click()
    
    On Error GoTo ErrHndlr
    
    'Print the 36 page report here
    '...
    '...
    '...
    
    
    'If you get here, there are no errors
    MsgBox "Printing Complete!"
    Unload Me
    Exit Sub
ErrHndlr:
    'If the error is caused by the cancel button
        Err.Clear
        MsgBox "Printing Cancelled!"
        Unload Me
    'Else - there is another type of error
        MsgBox "Put your message here!"
        Err.Clear
    'End If
    Unload Me   'Control back to PIF Control Menu
End Sub

'QUIT THE SPEC REPORT MENU
Private Sub cmdQuitSpecReports_Click()
    Unload Me
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[Dreamboat]

DSI's helping a lot, I see. So I don't know if this helps or not; it's from someone else who hasn't seen your code:

Tell him to look in all the Worksheet modules and workbook modules to make sure one of these Events aren't housing some code that is using the Sceenupdating. I still put money on this being the cause.


He is using:
Me Unload
isn't he ? to unload the form! And not Me.Hide
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[Guest_imported]

BUG: Microsoft Excel Does Not Repaint Properly with Automation (Q187745)

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Visual Basic for Applications version 5.0
Microsoft Office XP Developer
Microsoft Office 2000 Developer
Microsoft Excel 2002
Microsoft Excel 2000
Microsoft Excel 97 for Windows

--------------------------------------------------------------------------------


SYMPTOMS
When automating Microsoft Excel 97, Excel 2000, or Excel 2002, if the application window has been made visible and the user manually closes it, Excel will not repaint correctly the next time the application window is made visible again.



CAUSE
This problem occurs when a user attempts to quit a running instance of Excel while an Automation client still has a reference to the application object for that instance. By design, Excel does not quit an instance of itself unless all external references are released; if a user tries to quit Excel manually, the application window is merely hidden so that the Automation client may continue working. If, however, the Automation client attempts to make Excel visible again, the application window will not be displayed properly and repainting will not occur.



RESOLUTION
A workaround is to set the ScreenUpdating property of the application object to True after you have made the window visible. This will force Excel to repaint its client area so that it will be displayed properly.



STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.



MORE INFORMATION

Steps to Reproduce Behavior
Start a new Standard EXE project in Visual Basic. Form1 is created by default.


On the Project menu, click References, and then select the Microsoft Excel 8.0 object library. For Excel 2000, select Microsoft Excel 9.0 object library, and for Excel 2002, select the Microsoft Excel 10.0 object library.


Place a CommandButton on Form1.


Copy the following code to the Code Window of Form1:
Private oApp As Excel.Application

Private Sub Command1_Click()
oApp.Visible = True
End Sub

Private Sub Form_Load()
Set oApp = CreateObject("Excel.Application&quot
Command1.Caption = "Show Excel"
End Sub




Private Sub Form_Unload(Cancel As Integer)
Set oApp = Nothing
End Sub



On the Run menu, click Start, or press the F5 key to start the program.


Click on the Command button to make Excel visible. Close Excel by pressing the Close button on Excel's title bar, or by selecting Exit from the File menu. Now press the Visual Basic Command button again, and note that Excel does not paint itself correctly.


Repeat the steps again with the Command button's code modified as follows:
Private Sub Command1_Click()
oApp.Visible = True
oApp.ScreenUpdating = True
End Sub

 

[GSMike]

The right answer always has a certain feel to it.
Thanks, x983llsj198! Mike Kemp
kempm541145@yahoo.com