VBA Toolbox appears during macro execution....

GS7CLW

Banned
Joined
Aug 10, 2010
Messages
168
This macro works perfectly; however, the VBA Toolbox appears during the execution.....and I have NO ideas why?

Code:
Sub Green_Sheet()
'
' Opens the Green Sheet and Processing Efficiency for comparison
' Macro developed 29 Dec 2010 by Clifford Williams
UserForm2.Show
UserForm2.Label1.Caption = "Opening your report..."
UserForm2.Repaint
Dim Month As Date
Dim FileName As String
Month = (Date)
Application.ScreenUpdating = False ' turn OFF the screen updating
   If Month >= DateValue("9/16/2010") And Month < DateValue("10/16/2010") Then
        FileName = "RY 11 RY 11 OCT Green.xlsx"
    ElseIf Month >= DateValue("10/16/2010") And Month < DateValue("11/19/2010") Then
        FileName = "RY 11 NOV Green.xlsx"
    ElseIf Month >= DateValue("11/19/2010") And Month < DateValue("12/18/2010") Then
        FileName = "RY 11 DEC Green.xlsx"
    ElseIf Month >= DateValue("12/18/2010") And Month < DateValue("1/19/2011") Then
        FileName = "RY 11 Jan Green.xlsx"
    ElseIf Month >= DateValue("1/19/2011") And Month < DateValue("2/19/2011") Then
        FileName = "RY 11 Feb Green.xlsx"
    ElseIf Month >= DateValue("2/19/2011") And Month < DateValue("3/19/2011") Then
        FileName = "RY 11 Mar Green.xlsx"
    ElseIf Month >= DateValue("3/19/2011") And Month < DateValue("4/16/2011") Then
        FileName = "RY 11 Apr Green.xlsx"
    ElseIf Month >= DateValue("4/16/2011") And Month < DateValue("5/21/2011") Then
        FileName = "RY 11 May Green.xlsx"
    ElseIf Month >= DateValue("5/21/2011") And Month < DateValue("6/18/2011") Then
        FileName = "RY 11 June Green.xlsx"
    ElseIf Month >= DateValue("6/18/2011") And Month < DateValue("7/16/2011") Then
        FileName = "RY 11 July Green.xlsx"
    ElseIf Month >= DateValue("7/16/2011") And Month < DateValue("8/20/2011") Then
        FileName = "RY 11 Aug Green.xlsx"
    ElseIf Month >= DateValue("8/20/2011") And Month < DateValue("9/17/2011") Then
        FileName = "RY 11 Sep Green.xlsx"
    End If
    Workbooks.Open FileName:= _
         "[URL="file://\\XXXXXXXXXp\s3_data$\MEPSFLR\FLR"]\\XXXXXXXXXp\s3_data$\MEPSFLR\FLR[/URL] CDR\BDE_Reports\" + FileName, UpdateLinks:=0
    Sheets(" Disq").Select
    Columns("G:G").ColumnWidth = 5.75
    ActiveWindow.View = xlNormalView
    UserForm2.Label1.Caption = "Saving the Green Sheet..."
    UserForm2.Repaint
    ActiveWorkbook.Save
    UserForm2.Label1.Caption = "Printing the Green Sheet..."
    UserForm2.Repaint
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    ChDir "[URL="file://\\XXXXXX\s3_data$\MEPSFLR\FLR"]\\XXXXXX\s3_data$\MEPSFLR\FLR[/URL] CDR"
    UserForm2.Label1.Caption = "Opening the Processing Efficiency..."
    UserForm2.Repaint
    Workbooks.Open FileName:="[URL="file://\\XXXXXXX\s3_data$\MEPSFLR\FLR"]\\XXXXXXX\s3_data$\MEPSFLR\FLR[/URL] CDR\2011ProcEff.xlsm"
    UserForm2.Label1.Caption = "Saving the Green Sheet..."
    UserForm2.Repaint
    ActiveWorkbook.Save
    UserForm2.Label1.Caption = "Processing Complete..."
    UserForm2.Repaint
    Unload UserForm2
    ActiveWorkbook.Saved = True
    Application.ScreenUpdating = True ' turn ON the screen updating
End Sub

ANY ideas greatly appreciated!!!

Hope everyone is having a great week!
cliff
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
edit: oops

maybe also add to the start
Application.DisplayAlerts = False
and to the end
Application.DisplayAlerts = True
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
Does it still happen if you step through the code with F8?

If so, at which statement?
 

GS7CLW

Banned
Joined
Aug 10, 2010
Messages
168
Application.DisplayAlerts = False
doesn't stop it...

running F8:

the very first line:
UserForm2.Show
 

GS7CLW

Banned
Joined
Aug 10, 2010
Messages
168
You won't believe this:

1. opened UserForm2 in VBA
2. closed the VBA Toolbox (clicked the X)
3. saved the sheet
4. NOW the macro works perfectly......

Hope everyone has a great weekend.........IT'S FRIDAY!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,989
Messages
5,526,088
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top