Startup script (workbook_open) doesn't run

rhaverkate

New Member
Joined
Aug 28, 2018
Messages
2
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


Code:
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)


Code:
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
 
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I have tried all the proposed solutions in your suggested thread, but it doesn't work for me.

Any other suggestions I could try?
 
Upvote 0
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:
Code:
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:
Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

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