1

I have this code in VBA that builds a list of all currently opened workbooks, and adds them to a named range, so that I can use that as input for a selection list. This way the user is able to select the workbook that should be processed.

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

As a regular sub this works perfectly fine. When I try to run it as a Workbook_Open() sub it doesn't.

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

But no results are presented, nor any error messages.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
  • You need to paste the above code `Private Sub Workbook_Open()` in the `ThisWorkbook` code area – Siddharth Rout Jul 09 '21 at 08:13
  • The code should be in the `ThisWorkbook` Object Module as `Private Sub Workbook_Open()`. But this will run fast to count the workbooks open while still opening thisworkbok 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 – Naresh Jul 09 '21 at 08:14
  • 1
    Also if `"Werkboeken"` is not active then you will get error on the line `Set Rng = ws.Range(Cells(1, 1), Cells(i - 1, 1))`. You may want to fully qualify the cells as shown [HERE](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) – Siddharth Rout Jul 09 '21 at 08:15
  • Thanks @Naresh, that did the trick. If you want to add it as an answer I can accept it. – Luuklag Jul 09 '21 at 08:20
  • 1
    @SiddharthRout thanks for the tip, it is good practice to do so indeed, I dropped the ball on that here. Luckily there is only one worksheet in this folder so it hasn't caused problems (yet). – Luuklag Jul 09 '21 at 08:21

1 Answers1

2

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

Naresh
  • 2,984
  • 2
  • 9
  • 15