Startup script (workbook_open) doesn't run

[rhaverkate]

I have a workbook which contains a startup script which works when opening this workbook manually. Now this is a viewer and is sometimes changed by an other VBA script. People who use this viewer sometimes need to refresh this document without saving. The script below is written to reopen the workbook without saving. But now the startup script does not run, while it is reopened.

Thanks in advance.

---

Reopening script

Sub VerversPlanningsoverzicht()
'
' Rekent het planningsoverzicht opnieuw door


    'Pad op N schijf waar de bestanden zich bevinden
    wb = "Planningsoverzicht"


    ext = ".xlsm"
    
    pad = "N:\ALG_Productie\SPIR_Planning\Detailplanning"


    Application.DisplayAlerts = False
    
    Workbooks.Open (pad & wb & ext)
  
End Sub

---

Startup script (placed in ThisWorkbook)

Private Sub Workbook_Open()


' Put your code here
'Lengte planningsoverzicht
    Application.DisplayAlerts = False
    
    MsgBox ("test")
    
    If ActiveWorkbook.MultiUserEditing Then
        ActiveWorkbook.ExclusiveAccess
    End If
    
    ActiveSheet.Unprotect "spirplan17"
    
    Call Filter_deactivate
    
lpo = Application.WorksheetFunction.CountA(ActiveSheet.Range("j:j")) + 1
    Range("J3:v" & lpo).ClearContents
lpo = Application.WorksheetFunction.CountA(ActiveSheet.Range("a:a")) + 1


    If ActiveSheet.AutoFilterMode Then
        Selection.AutoFilter
    End If
      
        Range("J3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Buigmachines.xlsx]Buigmachines'!R3C3:R500c15,13,FALSE),""-"")"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Wikkelmachines.xlsx]Wikkelmachines'!R3C3:R500c15,13,FALSE),""-"")"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Buigwikkelmachines.xlsx]Buigwikkelmachines'!R3C3:R500c15,13,FALSE),""-"")"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Wikkelen_Handwerk.xlsx]Wikkelen_Handwerk'!R3C3:R500c15,13,FALSE),""-"")"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Ponserij.xlsx]Ponserij'!R3C3:R500c15,13,FALSE),""-"")"
    Range("O3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Laser_Zetwerk.xlsx]Laser_Zetwerk'!R3C3:R500c15,13,FALSE),""-"")"
    Range("P3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Laser_Zetwerk.xlsx]Laser_Zetwerk'!R3C3:R500c15,13,FALSE),""-"")"
    Range("Q3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Overig_Handwerk.xlsx]Overig_Handwerk'!R3C3:R500c15,13,FALSE),""-"")"
    Range("R3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Ogenafdeling.xlsx]Ogenafdeling'!R3C3:R500c15,13,FALSE),""-"")"
    Range("S3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Oven.xlsx]Oven'!R3C3:R500c15,13,FALSE),""-"")"
    Range("T3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Slijpafdeling.xlsx]Slijpafdeling'!R3C3:R500c15,13,FALSE),""-"")"
    Range("U3").Select
    ActiveCell.FormulaR1C1 = _
        "=iferror(VLOOKUP(RC1&R1C,'N:\ALG_Productie\SPIR_Planning\Detailplanning\[Expeditie.xlsx]Expeditie'!R3C3:R500c15,13,FALSE),""-"")"
    Range("J3:U3").Select
    If lpo > 3 Then
    Selection.AutoFill Destination:=Range("J3:U" & lpo)
    Range("J3: U" & lpo).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End If
    
    Range("A2:W2").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Viewer").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Viewer").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("E2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Viewer").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Call Filter_activate
    
    ActiveSheet.Protect Password:="spirplan17", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
        
    wb = "Planningsoverzicht"
    ext = ".xlsm"
    pad = "N:\ALG_Productie\SPIR_Planning\Detailplanning"
    
    ActiveWorkbook.SaveAs (pad & wb & ext), accessMode:=xlShared
    
    Application.DisplayAlerts = True
    
End Sub

 

[Russell Hauf]

Check out the bottom of this post: https://stackoverflow.com/questions/11369762/workbook-open-sub-wont-run-when-i-open-the-workbook

 

[rhaverkate]

I have tried all the proposed solutions in your suggested thread, but it doesn't work for me.

Any other suggestions I could try?

 

[Russell Hauf]

Hi - are you saying that you are trying to re-open the workbook that you are already in? If so, not sure why you can't save it and just call the workbook open code again, but maybe just calling workbook_open would run your code w/o saving what is there? So:

Sub VerversPlanningsoverzicht()
    Call Workbook_Open()
End Sub

If you really need to open a different copy of the workbook, I guess I'd try something like this:

Sub VerversPlanningsoverzicht()
'
    'Application.DisplayAlerts = False
    
    Workbooks.Open FileName:=ThisWorkbook.Fullname, ReadOnly:=True
    Workbooks(ThisWorkbook.Name).Close False
 
End Sub

You really don't want multiple copies of your file open at the same time (which is why I put the ReadOnly:=True in there). Not sure why you need to save the workbook in your workbook_open code if you don't want changes to be saved before you "refresh." More information might be good (in a new post), as there's probably a different way to do what you're trying to do that wouldn't be as complicated.