CODEWITHSUNDEEP
excelvba
Luuklag
Luuklag

Reputation: 3914

Run regular sub as Workbook_Open() event

This VBA code builds a list of all open workbooks, and adds them to a named range, so that I can use that as input for a selection list.

Sub Lijst_bouwen()

Dim i As Integer
Dim wb_pnt As Workbook
Set wb_pnt = Application.ThisWorkbook
Dim wb_overzicht As Workbook
Dim ws As Worksheet
Set ws = wb_pnt.Worksheets("Werkboeken")

For i = 1 To Workbooks.Count        'build list of open workbooks
    ws.Cells(i, 1) = Workbooks(i).Name
Next i

Dim Rng As Range
Set Rng = ws.Range(Cells(1, 1), Cells(i - 1, 1))
wb_pnt.Names.Add Name:="Lijst", RefersTo:=Rng

End Sub

I want to run this as a Workbook_Open() sub.

The first line of the code then becomes: Private Sub Workbook_Open()

No results are presented, nor any error messages.

Upvotes: 1

Views: 54

Answers (1)

Naresh
Naresh

Reputation: 3034

The code should be in the ThisWorkbook Object Module as Private Sub Workbook_Open(). But this will run fast to count the opened workbooks while still opening thisworkbook and will throw error I guess. So, it would be better to run with onTime as ..

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:02"), "Lijst_bouwen"
End Sub

... keeping Lijst_bouwen in module

Upvotes: 2

Related Questions