Workbook_Open Event Runs Too Soon

[Billy Hill]

I get a daily report emailed to me in Excel format. It's hidious to read so I made a macro to clean it up so I can run it when I opent he workbook.

There is only one sheet, named LockedSupplierReport, and I'd like to use the Workbook_Open event to test for this sheet name and run the macro automagically when it opens.

If I run the two lines in the debug window while the report is opened it works. But when I put the code in ThisWorkbook in the Workbook_Open function it seems to run before the report opens.

Private Sub Workbook_Open()
s = Sheets(1).Name
If s = "LockedSupplierReport" Then Call LockedSupplierReport
End Sub

To test this I changed the code to this, saved and closed excel, then re-opened the report.

Private Sub Workbook_Open()
s = Sheets(1).Name
MsgBox s
If s = "LockedSupplierReport" Then Call LockedSupplierReport
MsgBox "Didn't work, did it?"
End Sub

When I open it, Excel opens and pops the msg box up with "Sheet1" then "Didn't work", then it opens the report.

How can I get this code to run after the report is open?

 

[RoryA]

Move the code to a new routine in a normal module, then use:

Private Sub Workbook_Open()
Application.OnTime Now() + Timeserial(0,0,1), "new_routine_name_here"
End Sub

 

[Billy Hill]

Thank you. That worked.

Unfortunately, now when I exit the report without saving, I get two message boxes in a row saying

The picture is too large and will be truncated.

The file I'm working with is from 2003 XLS and I'm using Excel 2010. Some of the code I'm running is conditional formatting to highlight certain cells with pink BG and red FONTs, could it be that this conditional formatting isn't compatable with the older version? That almost doesn't make sense cuz I'm not trying to save the file.

 

[RoryA]

Without seeing the code, I couldn't even hazard a guess, other than that perhaps you need to clear the clipboard.

 

[Billy Hill]

All the code is in my Personal.XLSM file, and there are thousands of lines of code in scores of different macros. /shrugs/

 

[RoryA]

Sounds like a lot of code for some data cleanup to me.

 

[Billy Hill]

Sounds like a lot of code for some data cleanup to me.

Heh, it's not all for cleaning up some code. This company is working in the dark ages and me being the lazy SOB, err, I mean me being the efficient SOB I am, I automate the crap out of things.

We've got a 20 year old MRP system here that just. plain. sucks., so much of my code is designed to go around the MRP system and attach to the databases directly. Needless to say my IT department doesn't really like me because I keep doing things they tell me can't be done with our system...

 

[RoryA]

I would only need to see the code you use for this clean up, not all the code you own.

 

[Billy Hill]

Here it is...

Sub LockedSupplierReport()
'
' LockedSupplierReport Macro
'
'
    Columns("M:M").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("M:M").Select
    Selection.Cut
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "'Date"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "'BC"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "'PN"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "'VC"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "'L VC"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "'Quote$"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "'Locked$"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "'Diff$"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "'PO"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "'Line"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "'QTY"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "'QTY Due"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "'Due Date"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "'MRP Date"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "'PO Status"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "'PO Line Status"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "'COMM Code"
    Range("A2").Select
 
 
    Call BAFS
    'Conditional formatting
        Columns("F:F").Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Higher", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("F1").Select
    ActiveWorkbook.Worksheets("LockedSupplierReport").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("LockedSupplierReport").AutoFilter.Sort.SortFields. _
        Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LockedSupplierReport").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Sub BAFS()
'
' BAFS Macro
' Bold, Auto Filter, Freeze Cells and Size
'
    Rows("1:1").Select
    Selection.Font.Bold = True
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
    Call SizeCells
End Sub
 
Sub SizeCells()
' sizecells Macro
    Cells.Select
    Selection.RowHeight = 14.25
    Selection.ColumnWidth = 4.86
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub

 

[RoryA]

There's a lot of unnecessary selecting in there. You could try this:

Sub LockedSupplierReport()
'
' LockedSupplierReport Macro
'
'
    Dim wks As Worksheet
    Columns("M:M").Cut
    Columns("B:B").Insert Shift:=xlToRight
    Columns("M:M").Cut
    Columns("P:P").Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("A1:C1").Value = Array("'Date", "'BC", "'PN")
    Range("H1:N1").Value = Array("'VC", "'L VC", "'Quote$", "'Locked$", "'Diff$", "'PO", "'Line")
    Range("P1:U1").Value = Array("'QTY", "'QTY Due", "'Due Date", "'MRP Date", "'PO Status", "'PO Line Status")
    Range("X1").Value = "'COMM Code"
 
    Call BAFS
    'Conditional formatting
    With Columns("F:F").FormatConditions
        .Add Type:=xlTextString, String:="Higher", TextOperator:=xlContains
        .Item(.Count).SetFirstPriority
        With .Item(1)
            With .Font
                .Color = -16383844
                .TintAndShade = 0
            End With
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    End With
    Set wks = ActiveWorkbook.Worksheets("LockedSupplierReport")
    With wks.AutoFilter.Sort
        With .SortFields
            .Clear
            .Add Key:=wks.Range("A1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        End With
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Sub BAFS()
'
' BAFS Macro
' Bold, Auto Filter, Freeze Cells and Size
'
    With Rows("1:1")
        .Select
        .Font.Bold = True
        .AutoFilter
    End With
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
    Call SizeCells
End Sub
 
Sub SizeCells()
' sizecells Macro
    With Cells
        .RowHeight = 14.25
        .EntireColumn.AutoFit
    End With
    Range("A1").Select
End Sub