userform disappears when running subroutines and userform.repaint does not bring back the focus

S

sdohertyccb

Board Regular
Joined
Feb 15, 2005
Messages
91
I am using a userform that tracks the progress of a series of subroutines that process reports and saves them to a network directory. It works pretty well except at some point in the code it loses focus (disappears) and does not allow me to complete and exit the final process.

My Question is, is there something I can do to restore the Focus? It is initiated in MASTER workbook, and after everything has completed this is the only workbook still open, yet the form is not anywhere to be found.

I am using Windows 7 and Excel 2013 in a 64 bit environment. This worked fine in Excel 2010, so I am wondering if it has something to do with the Multi Document Interface (MDI) versus the Single Document Interface (SDI) of 2010.

Here is my Code:
Code: 
Private Sub Userform_Activate()
Dim Time1
Dim Time2
Dim Time3
Dim Time4
Dim Progress2 As String
Dim i As Integer
Dim c As Integer
On Error GoTo General_Error

    g_MinutesName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_Minutes.xlsx"
    g_AMGRWWhatIfName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_WhatIf.xlsm"
    g_ReportName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & ".xlsx"
    g_DBReportName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_BP.pdf"
    g_pdfName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & ".pdf"
    BudgetName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_Budget.xlsm"
    WhatIfFullName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_WhatIfFull.xlsm"
    HistoryFile = g_HistoryPath & g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_history (1-9)" & ".csv"
    HistoryFile2 = g_HistoryPath2 & g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_history (10-11)" & ".csv"
    ZipName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_Zip.zip"
    Audit_Name = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_Audit.xlsx"
    DB_MinutesName = g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_Minutes.xlsx"


g_ClientMasterALCO = g_ReportStorePath & g_ReportName
ReportPathName = g_ReportStorePath & g_ReportName
PdfPathName = g_ReportStorePath & g_pdfName
ZipPathName = g_ReportStorePath & ZipName
MinutesPathName = g_ReportStorePath & g_MinutesName
WhatIfPathName = g_ReportStorePath & g_AMGRWWhatIfName
AuditPathName = g_ReportStorePath & Audit_Name
BudgetPathName = g_ReportStorePath & BudgetName
WhatIfFullPathName = g_ReportStorePath & WhatIfFullName


    LabelGenerateInfo.FontName = "Tahoma"
    LabelGenerateInfo.FontSize = 12
    LabelGenerateInfo.Caption = "System is preparing to the print/save sequence; for " & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & "Please wait ..."
    LabelGenerateInfo.Visible = True
DoEvents
ReportPrint.Repaint

Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is creating the Table of Contents for: " & vbCrLf & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
DoEvents
ReportPrint.Repaint
Build_TOC

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is Creating and Loading the Audit File " & vbCrLf & vbCrLf & "for: " & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
auditOK = False
Save_Audit

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is Loading What If File " & vbCrLf & vbCrLf & "for: " & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
whatifOK = False
load_whatif

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is Loading the FULL What If File " & vbCrLf & vbCrLf & "for: " & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
whatiffullOK = False
SaveWhatIfFull

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is Loading the Budget File " & vbCrLf & vbCrLf & "for: " & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
budgetOK = False
If g_BP_Budget.Windows(1).Visible = False Then g_BP_Budget.Windows(1).Visible = True
SaveBudget

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is Printing PDF File " & vbCrLf & vbCrLf & "for: " & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
PDFOk = False
PrintPDF

Time2 = Time
    g_AMGRWTemplate.Worksheets("log_qptime").Cells(2, 6).Value = "PrintPDF Time"
    g_AMGRWTemplate.Worksheets("log_qptime").Cells(2, 7).Value = time_diff2(Time1, Time2)

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is saving ALCO File to the 'reports' path " & vbCrLf & vbCrLf & "for:" & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & "as:" & vbCrLf & g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & ".xlsx" & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
alcoOk = False
SaveMasterALCO

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is saving History File(s) to the 'database queue' " & vbCrLf & vbCrLf & "for:" & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
histOk = False
SaveHistory

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is saving Minutes File to the 'minutes' path " & vbCrLf & vbCrLf & "for:" & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & "as:" & vbCrLf & g_MinutesPath & g_BankInfo.file_path & "\minutes\" & g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & "_minutes" & ".xlsx" & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
minutesOk = False
SaveMinutes

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "time: " & time_diff2(Time3, g_StartTime)
    LabelGenerateInfo.Caption = "System is creating ZIP File to the 'reports' path " & vbCrLf & vbCrLf & "for:" & vbCrLf & g_CurrentBank & " Record Date: " & g_RecordDate2 & vbCrLf & vbCrLf & "as:" & vbCrLf & g_RecordDate2 & "_" & g_BankID & "_" & g_BankInfo.short_name & ".zip" & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
zipOk = False
XPCompress

DoEvents
ReportPrint.Repaint
If Not (g_AMGRWTemplate Is Nothing) Then
    g_AMGRWTemplate.Close SaveChanges:=False
    Set g_AMGRWTemplate = Nothing
End If

DoEvents
ReportPrint.Repaint
Time3 = Time
    Progress2 = "TOTAL TIME: " & time_diff2(Time3, g_StartTime)
If (PDFOk = False) Then
    LabelGenerateInfo.Caption = "INCOMPLETE - There were errors in printing this report. " & vbCrLf & "Please check the error log for details. " & vbCrLf & vbCrLf & Progress2
    LabelGenerateInfo.Visible = True
Else
    LabelGenerateInfo.FontName = "Tahoma"
    LabelGenerateInfo.FontSize = 9

DoEvents
ReportPrint.Repaint
If (histOk = True And alcoOk = True And minutesOk = True And zipOk = True And whatifOK = True And auditOK = True And whatiffullOK = True And budgetOK = True) Then
    LabelGenerateInfo.Caption = "COMPLETE - All operations have been successfully completed." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "The corresponding pdf file was saved in: " & vbCrLf & PdfPathName & vbCrLf & vbCrLf & "The corresponding ALCO file was saved in: " & vbCrLf & g_ClientMasterALCO & vbCrLf & vbCrLf & "The corresponding minutes file was saved in: " & vbCrLf & MinutesPathName & vbCrLf & vbCrLf & "The corresponding history file was saved in: " & vbCrLf & HistoryFile & vbCrLf & vbCrLf & "The corresponding ZIP file was save in:  " & vbCrLf & ZipPathName & vbCrLf & vbCrLf & "The corresponding What If file was save in:  " & vbCrLf & WhatIfPathName & vbCrLf & vbCrLf & vbCrLf & Progress2
End If

DoEvents
ReportPrint.Repaint
If (histOk = False Or alcoOk = False Or minutesOk = False Or zipOk = False Or whatifOK = False Or auditOK = False Or whatiffullOK = False Or budgetOK = False) Then
    LabelGenerateInfo.Caption = "INCOMPLETE - Some operations have been completed; Check File Directory to determine which files did not complete." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & Progress2
End If

LabelGenerateInfo.Visible = True
End If

Application.Wait (Now + TimeValue("0:00:01"))
Application.Visible = False
Application.Visible = True
DoEvents
ReportPrint.Repaint

Exit Sub
General_Error:
    MsgBox Title:="Caution!", Prompt:="Error in Sub(UserForm_Activate) of Form (ReportPrint) " & vbCrLf & vbCrLf & "Err.Number:  " & Err.Number & vbCrLf & "Err.Source: " & Err.Source & vbCrLf & "Err.Description: " & Err.description, Buttons:=vbExclamation

End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
I have decided to simplify this code to the bare minimum, so hopefully, someone can help me out with this problem.
Thanks in advance for taking a look at this for me.
UserForm name is ReportPrint, and I have tried using some of the tricks posted in this Forum to re-focus the UserForm but none seem to work, like DoEvents, RePaint, Application.Wait and Application.Visible...
Any help would be Greatly Appreciated...
Thanks

Code: 
Dim Time1
Dim Time2
Dim Time3
Dim Time4
Dim Progress2 As String
Dim i As Integer
Dim c As Integer
On Error GoTo General_Error

'>--- UserForm Name is ReportPrint ----

DoEvents
ReportPrint.Repaint
Call Build_TOC

DoEvents
ReportPrint.Repaint
Call Save_Audit

DoEvents
ReportPrint.Repaint
Call load_whatif

DoEvents
ReportPrint.Repaint
Call SaveWhatIfFull

DoEvents
ReportPrint.Repaint
Call SaveBudget

DoEvents
ReportPrint.Repaint
Call PrintPDF

DoEvents
ReportPrint.Repaint
Call SaveMasterALCO

DoEvents
ReportPrint.Repaint
Call SaveHistory

DoEvents
ReportPrint.Repaint
Call SaveMinutes

DoEvents
ReportPrint.Repaint
Call XPCompress

DoEvents
ReportPrint.Repaint

Application.Wait (Now + TimeValue("0:00:01"))
Application.Visible = False
Application.Visible = True
DoEvents
ReportPrint.Repaint

Exit Sub
General_Error:
    MsgBox Title:="Caution!", Prompt:="Error in Sub(UserForm_Activate) of Form (ReportPrint) " & vbCrLf & vbCrLf & "Err.Number:  " & Err.Number & vbCrLf & "Err.Source: " & Err.Source & vbCrLf & "Err.Description: " & Err.description, Buttons:=vbExclamation

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Reflections Code revised to run in This Session module
Replies
0
Views
1K
Kurt
K

Forum statistics

Threads
1,221,808
Messages
6,162,097
Members
451,742
Latest member
JuanMark10

Share this page

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